libname inn 'D:\Amazon Price Dynamics\New Data\Data Scraped\Home_Kitchen\Cluster Analysis';


%macro filename (a,b);
data &b.    ;
     %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
     infile "D:\Amazon Price Dynamics\New Data\Data Scraped\Home_Kitchen\merged\&&a..csv" delimiter = ',' MISSOVER DSD
 lrecl=32767 firstobs=2 ;
        informat Time anydtdtm40. ;
        informat ASIN $10. ;
        informat Product_Description__same_as_the $133. ;
        informat Price $32. ;
        informat Condition___New $4. ;
        informat Free_Shipping__Y_N_ $13. ;
        informat Buy_Box_Price $32. ;
        informat Product_Star_Rating $50. ;
        informat __Customer_Reviews_on_Product $50. ;
        informat __Answered_Questions $50. ;
        informat In_Stock__Indicator_Y_N_NA_varia $9. ;
        informat Count_in_stock__if_available_ $3. ;
        informat Fulfilled_by_Amazon__Y_N_ $5. ;
        informat Amazon_Prime__Y_N_ $5. ;
        informat Sales_Rank_in_Own_Category $30. ;
        informat Sales_Rank_in_Sub__Category $30. ;
        informat Indicator_variable__Focal_Produc $1. ;
        informat Indicator_variable__Sponsored_Pr $1. ;
        informat Indicator_variable__Competitor_P $1. ;
        informat Indicator_variable__Similar_Item $1. ;
        informat Seller_ID $14. ;
        informat Seller_Rating $32. ;
        informat __Positive_Ratings $32. ;
        informat __Ratings $9. ;
        format Time datetime. ;
        format ASIN $10. ;
        format Product_Description__same_as_the $133. ;
        format Price $32. ;
        format Condition___New $4. ;
        format Free_Shipping__Y_N_ $13. ;
        format Buy_Box_Price $32. ;
        format Product_Star_Rating $50. ;
        format __Customer_Reviews_on_Product $50. ;
        format __Answered_Questions $50. ;
        format In_Stock__Indicator_Y_N_NA_varia $9. ;
        format Count_in_stock__if_available_ $3. ;
        format Fulfilled_by_Amazon__Y_N_ $5. ;
        format Amazon_Prime__Y_N_ $5. ;
        format Sales_Rank_in_Own_Category $30. ;
        format Sales_Rank_in_Sub__Category $30. ;
        format Indicator_variable__Focal_Produc $1. ;
        format Indicator_variable__Sponsored_Pr $1. ;
        format Indicator_variable__Competitor_P $1. ;
        format Indicator_variable__Similar_Item $1. ;
        format Seller_ID $14. ;
        format Seller_Rating $32. ;
        format __Positive_Ratings $32. ;
        format __Ratings $9. ;
     input
                 Time
                 ASIN $
                 Product_Description__same_as_the $
                 Price $
                 Condition___New $
                 Free_Shipping__Y_N_ $
                 Buy_Box_Price $
                 Product_Star_Rating $
                 __Customer_Reviews_on_Product $
                 __Answered_Questions $
                 In_Stock__Indicator_Y_N_NA_varia $
                 Count_in_stock__if_available_ $
                 Fulfilled_by_Amazon__Y_N_ $
                 Amazon_Prime__Y_N_ $
                 Sales_Rank_in_Own_Category $
                 Sales_Rank_in_Sub__Category $
                 Indicator_variable__Focal_Produc $
                 Indicator_variable__Sponsored_Pr $
                 Indicator_variable__Competitor_P $
                 Indicator_variable__Similar_Item $
                 Seller_ID $
                 Seller_Rating $
                 __Positive_Ratings $
                 __Ratings $
     ;
     if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
     run;

proc append base=stack_alldata1 data= &b.;
run;

%mend;
%filename(2017-9-13, Sep13);
%filename(2017-9-14, Sep14);
%filename(2017-9-15, Sep15);
%filename(2017-9-16, Sep16);
%filename(2017-9-17, Sep17);
%filename(2017-9-18, Sep18);
%filename(2017-9-19, Sep19);
%filename(2017-9-20, Sep20);
%filename(2017-9-21, Sep21);
%filename(2017-9-22, Sep22);
%filename(2017-9-23, Sep23);
%filename(2017-9-24, Sep24);
%filename(2017-9-25, Sep25);
%filename(2017-9-26, Sep26);
%filename(2017-9-27, Sep27);
%filename(2017-9-28, Sep28);
%filename(2017-9-29, Sep29);
%filename(2017-9-30, Sep30);
%filename(2017-10-1, Oct1);
%filename(2017-10-2, Oct2);
%filename(2017-10-3, Oct3);
%filename(2017-10-4, Oct4);
%filename(2017-10-5, Oct5);
%filename(2017-10-6, Oct6);
%filename(2017-10-7, Oct7);
%filename(2017-10-8, Oct8);
%filename(2017-10-9, Oct9);
%filename(2017-10-10, Oct10);
%filename(2017-10-11, Oct11);
%filename(2017-10-12, Oct12);
%filename(2017-10-13, Oct13);
%filename(2017-10-14, Oct14);
%filename(2017-10-15, Oct15);
%filename(2017-10-16, Oct16);
%filename(2017-10-17, Oct17);
%filename(2017-10-18, Oct18);
%filename(2017-10-19, Oct19);
%filename(2017-10-20, Oct20);
%filename(2017-10-21, Oct21);
%filename(2017-10-22, Oct22);
%filename(2017-10-23, Oct23);
%filename(2017-10-24, Oct24);
%filename(2017-10-25, Oct25);
%filename(2017-10-26, Oct26);
%filename(2017-10-27, Oct27);
%filename(2017-10-28, Oct28);
%filename(2017-10-29, Oct29);
%filename(2017-10-30, Oct30);
%filename(2017-10-31, Oct31);
proc print data = stack_alldata1 (obs=10);
run;

%macro filename (a,b);
data &b.    ;
     %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
     infile "D:\Amazon Price Dynamics\New Data\Data Scraped\Home_Kitchen\merged\&&a..csv" delimiter = ',' MISSOVER DSD
 lrecl=32767 firstobs=2 ;
        informat Time anydtdtm40. ;
        informat ASIN $10. ;
        informat Product_Description__same_as_the $133. ;
        informat Price $32. ;
        informat Condition___New $4. ;
        informat Free_Shipping__Y_N_ $13. ;
        informat Buy_Box_Price $32. ;
        informat Product_Star_Rating $50. ;
        informat __Customer_Reviews_on_Product $50. ;
        informat __Answered_Questions $50. ;
        informat In_Stock__Indicator_Y_N_NA_varia $9. ;
        informat Count_in_stock__if_available_ $3. ;
        informat Fulfilled_by_Amazon__Y_N_ $5. ;
        informat Amazon_Prime__Y_N_ $5. ;
        informat Sales_Rank_in_Own_Category $30. ;
        informat Sales_Rank_in_Sub__Category $30. ;
        informat Indicator_variable__Focal_Produc $1. ;
        informat Indicator_variable__Sponsored_Pr $1. ;
        informat Indicator_variable__Competitor_P $1. ;
        informat Indicator_variable__Similar_Item $1. ;
        informat Seller_ID $14. ;
        informat Seller_Rating $32. ;
        informat __Positive_Ratings $32. ;
        informat __Ratings $9. ;
        format Time datetime. ;
        format ASIN $10. ;
        format Product_Description__same_as_the $133. ;
        format Price $32. ;
        format Condition___New $4. ;
        format Free_Shipping__Y_N_ $13. ;
        format Buy_Box_Price $32. ;
        format Product_Star_Rating $50. ;
        format __Customer_Reviews_on_Product $50. ;
        format __Answered_Questions $50. ;
        format In_Stock__Indicator_Y_N_NA_varia $9. ;
        format Count_in_stock__if_available_ $3. ;
        format Fulfilled_by_Amazon__Y_N_ $5. ;
        format Amazon_Prime__Y_N_ $5. ;
        format Sales_Rank_in_Own_Category $30. ;
        format Sales_Rank_in_Sub__Category $30. ;
        format Indicator_variable__Focal_Produc $1. ;
        format Indicator_variable__Sponsored_Pr $1. ;
        format Indicator_variable__Competitor_P $1. ;
        format Indicator_variable__Similar_Item $1. ;
        format Seller_ID $14. ;
        format Seller_Rating $32. ;
        format __Positive_Ratings $32. ;
        format __Ratings $9. ;
     input
                 Time
                 ASIN $
                 Product_Description__same_as_the $
                 Price $
                 Condition___New $
                 Free_Shipping__Y_N_ $
                 Buy_Box_Price $
                 Product_Star_Rating $
                 __Customer_Reviews_on_Product $
                 __Answered_Questions $
                 In_Stock__Indicator_Y_N_NA_varia $
                 Count_in_stock__if_available_ $
                 Fulfilled_by_Amazon__Y_N_ $
                 Amazon_Prime__Y_N_ $
                 Sales_Rank_in_Own_Category $
                 Sales_Rank_in_Sub__Category $
                 Indicator_variable__Focal_Produc $
                 Indicator_variable__Sponsored_Pr $
                 Indicator_variable__Competitor_P $
                 Indicator_variable__Similar_Item $
                 Seller_ID $
                 Seller_Rating $
                 __Positive_Ratings $
                 __Ratings $
     ;
     if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
     run;

proc append base=stack_alldata2 data= &b.;
run;

%mend;
%filename(2017-11-1, Nov1);
%filename(2017-11-2, Nov2);
%filename(2017-11-3, Nov3);
%filename(2017-11-4, Nov4);
%filename(2017-11-5, Nov5);
%filename(2017-11-6, Nov6);
%filename(2017-11-7, Nov7);
%filename(2017-11-8, Nov8);
%filename(2017-11-9, Nov9);
%filename(2017-11-10, Nov10);
%filename(2017-11-11, Nov11);
%filename(2017-11-12, Nov12);
%filename(2017-11-13, Nov13);

%macro filename (a,b);
data &b.    ;
     %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
     infile "D:\Amazon Price Dynamics\New Data\Data Scraped\Home_Kitchen\merged\&&a..csv" delimiter = ',' MISSOVER DSD
 lrecl=32767 firstobs=2 ;
        informat Time anydtdtm40. ;
        informat ASIN $10. ;
        informat Product_Description__same_as_the $133. ;
        informat Price $32. ;
        informat Condition___New $4. ;
        informat Free_Shipping__Y_N_ $13. ;
        informat Buy_Box_Price $32. ;
        informat Product_Star_Rating $50. ;
        informat __Customer_Reviews_on_Product $50. ;
        informat __Answered_Questions $50. ;
        informat In_Stock__Indicator_Y_N_NA_varia $9. ;
        informat Count_in_stock__if_available_ $3. ;
        informat Fulfilled_by_Amazon__Y_N_ $5. ;
        informat Amazon_Prime__Y_N_ $5. ;
        informat Sales_Rank_in_Own_Category $30. ;
        informat Sales_Rank_in_Sub__Category $30. ;
		informat Has_Discount_by_Amazon $5. ;
        informat Amazon_Discount_Value $5. ;
        informat Indicator_variable__Focal_Produc $1. ;
        informat Indicator_variable__Sponsored_Pr $1. ;
        informat Indicator_variable__Competitor_P $1. ;
        informat Indicator_variable__Similar_Item $1. ;
        informat Seller_ID $14. ;
        informat Seller_Rating $32. ;
        informat __Positive_Ratings $32. ;
        informat __Ratings $9. ;
        format Time datetime. ;
        format ASIN $10. ;
        format Product_Description__same_as_the $133. ;
        format Price $32. ;
        format Condition___New $4. ;
        format Free_Shipping__Y_N_ $13. ;
        format Buy_Box_Price $32. ;
        format Product_Star_Rating $50. ;
        format __Customer_Reviews_on_Product $50. ;
        format __Answered_Questions $50. ;
        format In_Stock__Indicator_Y_N_NA_varia $9. ;
        format Count_in_stock__if_available_ $3. ;
        format Fulfilled_by_Amazon__Y_N_ $5. ;
        format Amazon_Prime__Y_N_ $5. ;
        format Sales_Rank_in_Own_Category $30. ;
        format Sales_Rank_in_Sub__Category $30. ;
		format Has_Discount_by_Amazon $5. ;
        format Amazon_Discount_Value $5. ;
        format Indicator_variable__Focal_Produc $1. ;
        format Indicator_variable__Sponsored_Pr $1. ;
        format Indicator_variable__Competitor_P $1. ;
        format Indicator_variable__Similar_Item $1. ;
        format Seller_ID $14. ;
        format Seller_Rating $32. ;
        format __Positive_Ratings $32. ;
        format __Ratings $9. ;
     input
                 Time
                 ASIN $
                 Product_Description__same_as_the $
                 Price $
                 Condition___New $
                 Free_Shipping__Y_N_ $
                 Buy_Box_Price $
                 Product_Star_Rating $
                 __Customer_Reviews_on_Product $
                 __Answered_Questions $
                 In_Stock__Indicator_Y_N_NA_varia $
                 Count_in_stock__if_available_ $
                 Fulfilled_by_Amazon__Y_N_ $
                 Amazon_Prime__Y_N_ $
                 Sales_Rank_in_Own_Category $
                 Sales_Rank_in_Sub__Category $
				 Has_Discount_by_Amazon $
				 Amazon_Discount_Value $
                 Indicator_variable__Focal_Produc $
                 Indicator_variable__Sponsored_Pr $
                 Indicator_variable__Competitor_P $
                 Indicator_variable__Similar_Item $
                 Seller_ID $
                 Seller_Rating $
                 __Positive_Ratings $
                 __Ratings $
     ;
     if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
     run;

proc append base=stack_alldata3 data= &b.;
run;

%mend;
%filename(2017-11-15, Nov15);
%filename(2017-11-16, Nov16);
%filename(2017-11-17, Nov17);
%filename(2017-11-18, Nov18);
%filename(2017-11-19, Nov19);
%filename(2017-11-20, Nov20);
%filename(2017-11-21, Nov21);
%filename(2017-11-22, Nov22);
%filename(2017-11-23, Nov23);
%filename(2017-11-24, Nov24);
%filename(2017-11-25, Nov25);
%filename(2017-11-26, Nov26);
%filename(2017-11-27, Nov27);
%filename(2017-11-28, Nov28);
%filename(2017-11-29, Nov29);
%filename(2017-11-30, Nov30);
%filename(2017-12-1, Dec1);
%filename(2017-12-2, Dec2);
%filename(2017-12-3, Dec3);
%filename(2017-12-4, Dec4);
%filename(2017-12-5, Dec5);
/*%filename(2017-12-6, Dec6);*/
proc freq data = stack_alldata3;
tables Has_Discount_by_Amazon ;
run;
%macro filename (a,b);
data &b.    ;
     %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
     infile "D:\Amazon Price Dynamics\New Data\Data Scraped\Home_Kitchen\merged\&&a..csv" delimiter = ',' MISSOVER DSD
 lrecl=32767 firstobs=2 ;
        informat Time anydtdtm40. ;
        informat ASIN $10. ;
        informat Product_Description__same_as_the $133. ;
        informat Price $32. ;
		informat Condition $23. ;
        informat Condition___New $4. ;
        informat Free_Shipping__Y_N_ $13. ;
        informat Buy_Box_Price $32. ;
        informat Product_Star_Rating $50. ;
        informat __Customer_Reviews_on_Product $50. ;
        informat __Answered_Questions $50. ;
        informat In_Stock__Indicator_Y_N_NA_varia $9. ;
        informat Count_in_stock__if_available_ $3. ;
        informat Fulfilled_by_Amazon__Y_N_ $5. ;
        informat Amazon_Prime__Y_N_ $5. ;
        informat Sales_Rank_in_Own_Category $30. ;
        informat Sales_Rank_in_Sub__Category $30. ;
	    informat Has_Discount_by_Amazon $5. ;
        informat Amazon_Discount_Value $3. ;
        informat Indicator_variable__Focal_Produc $1. ;
        informat Indicator_variable__Sponsored_Pr $1. ;
        informat Indicator_variable__Competitor_P $1. ;
        informat Indicator_variable__Similar_Item $1. ;
        informat Seller_ID $14. ;
        informat Seller_Rating $32. ;
        informat __Positive_Ratings $32. ;
        informat __Ratings $9. ;
        format Time datetime. ;
        format ASIN $10. ;
        format Product_Description__same_as_the $133. ;
        format Price $32. ;
		format Condition $23. ;
        format Condition___New $4. ;
        format Free_Shipping__Y_N_ $13. ;
        format Buy_Box_Price $32. ;
        format Product_Star_Rating $50. ;
        format __Customer_Reviews_on_Product $50. ;
        format __Answered_Questions $50. ;
        format In_Stock__Indicator_Y_N_NA_varia $9. ;
        format Count_in_stock__if_available_ $3. ;
        format Fulfilled_by_Amazon__Y_N_ $5. ;
        format Amazon_Prime__Y_N_ $5. ;
        format Sales_Rank_in_Own_Category $30. ;
        format Sales_Rank_in_Sub__Category $30. ;
		format Has_Discount_by_Amazon $5. ;
        format Amazon_Discount_Value $3. ;
        format Indicator_variable__Focal_Produc $1. ;
        format Indicator_variable__Sponsored_Pr $1. ;
        format Indicator_variable__Competitor_P $1. ;
        format Indicator_variable__Similar_Item $1. ;
        format Seller_ID $14. ;
        format Seller_Rating $32. ;
        format __Positive_Ratings $32. ;
        format __Ratings $9. ;
     input
                 Time
                 ASIN $
                 Product_Description__same_as_the $
                 Price $
				 Condition $
                 Condition___New $
                 Free_Shipping__Y_N_ $
                 Buy_Box_Price $
                 Product_Star_Rating $
                 __Customer_Reviews_on_Product $
                 __Answered_Questions $
                 In_Stock__Indicator_Y_N_NA_varia $
                 Count_in_stock__if_available_ $
                 Fulfilled_by_Amazon__Y_N_ $
                 Amazon_Prime__Y_N_ $
                 Sales_Rank_in_Own_Category $
                 Sales_Rank_in_Sub__Category $
				 Has_Discount_by_Amazon $
                 Amazon_Discount_Value $
                 Indicator_variable__Focal_Produc $
                 Indicator_variable__Sponsored_Pr $
                 Indicator_variable__Competitor_P $
                 Indicator_variable__Similar_Item $
                 Seller_ID $
                 Seller_Rating $
                 __Positive_Ratings $
                 __Ratings $
     ;
     if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
     run;

proc append base=stack_alldata4 data= &b.;
run;
%mend;
%filename(2017-12-7, Dec7);
%filename(2017-12-8, Dec8);
%filename(2017-12-9, Dec9);
%filename(2017-12-10, Dec10);
%filename(2017-12-11, Dec11);
%filename(2017-12-12, Dec12);
%filename(2017-12-13, Dec13);
%filename(2017-12-14, Dec14);
%filename(2017-12-15, Dec15);
%filename(2017-12-16, Dec16);
%filename(2017-12-17, Dec17);
%filename(2017-12-18, Dec18);
%filename(2017-12-19, Dec19);
%filename(2017-12-20, Dec20);
%filename(2017-12-21, Dec21);
%filename(2017-12-22, Dec22);
%filename(2017-12-23, Dec23);
%filename(2017-12-24, Dec24);
%filename(2017-12-25, Dec25);
%filename(2017-12-26, Dec26);
%filename(2017-12-27, Dec27);
%filename(2017-12-28, Dec28);
%filename(2017-12-29, Dec29);
%filename(2017-12-30, Dec30);

proc contents data = stack_alldata5;
run;

%macro filename (a,b);
data &b.   ;
     %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
     infile "D:\Amazon Price Dynamics\New Data\Data Scraped\Home_Kitchen\merged\&&a..csv" delimiter = ',' MISSOVER DSD
 lrecl=32767 firstobs=2 ;
        informat Time anydtdtm40. ;
        informat ASIN $10. ;
        informat Product_Description__same_as_the $133. ;
        informat Price $32. ;
		informat Condition $23. ;
        informat Condition___New $4. ;
        informat Free_Shipping__Y_N_ $13. ;
        informat Buy_Box_Price $32. ;
        informat Product_Star_Rating $50. ;
        informat __Customer_Reviews_on_Product $50. ;
        informat __Answered_Questions $50. ;
        informat In_Stock__Indicator_Y_N_NA_varia $9. ;
        informat Count_in_stock__if_available_ $3. ;
        informat Fulfilled_by_Amazon__Y_N_ $5. ;
        informat Amazon_Prime__Y_N_ $5. ;
        informat Sales_Rank_in_Own_Category $30. ;
        informat Sales_Rank_in_Sub__Category $30. ;
	    informat Has_Discount_by_Amazon $5. ;
        informat Amazon_Discount_Value $3. ;
        informat Indicator_variable__Focal_Produc $1. ;
        informat Indicator_variable__Sponsored_Pr $1. ;
        informat Indicator_variable__Competitor_P $1. ;
        informat Indicator_variable__Similar_Item $1. ;
        informat Seller_ID $14. ;
        informat Seller_Rating $32. ;
        informat __Positive_Ratings $32. ;
        informat __Ratings $9. ;
        format Time datetime. ;
        format ASIN $10. ;
        format Product_Description__same_as_the $133. ;
        format Price $32. ;
		format Condition $23. ;
        format Condition___New $4. ;
        format Free_Shipping__Y_N_ $13. ;
        format Buy_Box_Price $32. ;
        format Product_Star_Rating $50. ;
        format __Customer_Reviews_on_Product $50. ;
        format __Answered_Questions $50. ;
        format In_Stock__Indicator_Y_N_NA_varia $9. ;
        format Count_in_stock__if_available_ $3. ;
        format Fulfilled_by_Amazon__Y_N_ $5. ;
        format Amazon_Prime__Y_N_ $5. ;
        format Sales_Rank_in_Own_Category $30. ;
        format Sales_Rank_in_Sub__Category $30. ;
		format Has_Discount_by_Amazon $5. ;
        format Amazon_Discount_Value $3. ;
        format Indicator_variable__Focal_Produc $1. ;
        format Indicator_variable__Sponsored_Pr $1. ;
        format Indicator_variable__Competitor_P $1. ;
        format Indicator_variable__Similar_Item $1. ;
        format Seller_ID $14. ;
        format Seller_Rating $32. ;
        format __Positive_Ratings $32. ;
        format __Ratings $9. ;
     input
                 Time
                 ASIN $
                 Product_Description__same_as_the $
                 Price $
				 Condition $
                 Condition___New $
                 Free_Shipping__Y_N_ $
                 Buy_Box_Price $
                 Product_Star_Rating $
                 __Customer_Reviews_on_Product $
                 __Answered_Questions $
                 In_Stock__Indicator_Y_N_NA_varia $
                 Count_in_stock__if_available_ $
                 Fulfilled_by_Amazon__Y_N_ $
                 Amazon_Prime__Y_N_ $
                 Sales_Rank_in_Own_Category $
                 Sales_Rank_in_Sub__Category $
				 Has_Discount_by_Amazon $
                 Amazon_Discount_Value $
                 Indicator_variable__Focal_Produc $
                 Indicator_variable__Sponsored_Pr $
                 Indicator_variable__Competitor_P $
                 Indicator_variable__Similar_Item $
                 Seller_ID $
                 Seller_Rating $
                 __Positive_Ratings $
                 __Ratings $
     ;
     if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
     run;

proc append base=stack_alldata5 data= &b.;
run;
%mend;
%filename(2018-1-1, Jan1);
%filename(2018-1-2, Jan2);
%filename(2018-1-3, Jan3);
%filename(2018-1-4, Jan4);
%filename(2018-1-5, Jan5);
%filename(2018-1-6, Jan6);
%filename(2018-1-7, Jan7);
%filename(2018-1-8, Jan8);
%filename(2018-1-9, Jan9);
%filename(2018-1-10, Jan10);
%filename(2018-1-11, Jan11);
%filename(2018-1-12, Jan12);
%filename(2018-1-13, Jan13);
%filename(2018-1-14, Jan14);
%filename(2018-1-15, Jan15);
%filename(2018-1-16, Jan16);
%filename(2018-1-17, Jan17);
%filename(2018-1-18, Jan18);
%filename(2018-1-19, Jan19);
%filename(2018-1-20, Jan20);
%filename(2018-1-21, Jan21);
%filename(2018-1-22, Jan22);
%filename(2018-1-23, Jan23);
%filename(2018-1-24, Jan24);
%filename(2018-1-25, Jan25);
%filename(2018-1-26, Jan26);
%filename(2018-1-27, Jan27);
%filename(2018-1-28, Jan28);
%filename(2018-1-29, Jan29);
%filename(2018-1-30, Jan30);
%filename(2018-1-31, Jan31);
%filename(2018-2-1, Feb1);
%filename(2018-2-2, Feb2);
%filename(2018-2-3, Feb3);
%filename(2018-2-4, Feb4);
%filename(2018-2-5, Feb5);
%filename(2018-2-6, Feb6);
%filename(2018-2-7, Feb7);
%filename(2018-2-8, Feb8);
%filename(2018-2-9, Feb9);
%filename(2018-2-10, Feb10);
%filename(2018-2-11, Feb11);
%filename(2018-2-12, Feb12);
%filename(2018-2-13, Feb13);
%filename(2018-2-14, Feb14);
%filename(2018-2-15, Feb15);
%filename(2018-2-16, Feb16);
%filename(2018-2-17, Feb17);
%filename(2018-2-18, Feb18);
%filename(2018-2-19, Feb19);
%filename(2018-2-20, Feb20);
%filename(2018-2-21, Feb21);
%filename(2018-2-22, Feb22);
%filename(2018-2-23, Feb23);
%filename(2018-2-24, Feb24);
%filename(2018-2-25, Feb25);
%filename(2018-2-26, Feb26);
%filename(2018-2-27, Feb27);
%filename(2018-2-28, Feb28);
%filename(2018-3-1, Mar1);
%filename(2018-3-2, Mar2);
%filename(2018-3-3, Mar3);
%filename(2018-3-4, Mar4);
%filename(2018-3-5, Mar5);
%filename(2018-3-6, Mar6);
%filename(2018-3-7, Mar7);
%filename(2018-3-8, Mar8);
%filename(2018-3-9, Mar9);
%filename(2018-3-10, Mar10);
%filename(2018-3-11, Mar11);
%filename(2018-3-12, Mar12);
%filename(2018-3-13, Mar13);
%filename(2018-3-14, Mar14);
%filename(2018-3-15, Mar15);
%filename(2018-3-16, Mar16);
%filename(2018-3-17, Mar17);
%filename(2018-3-18, Mar18);
%filename(2018-3-19, Mar19);
%filename(2018-3-20, Mar20);
%filename(2018-3-21, Mar21);
%filename(2018-3-22, Mar22);
%filename(2018-3-23, Mar23);
%filename(2018-3-24, Mar24);
%filename(2018-3-25, Mar25);
%filename(2018-3-26, Mar26);
%filename(2018-3-27, Mar27);
%filename(2018-3-28, Mar28);
%filename(2018-3-29, Mar29);
%filename(2018-3-30, Mar30);
%filename(2018-3-31, Mar31);
%filename(2018-4-1, Apr1);
%filename(2018-4-2, Apr2);
%filename(2018-4-3, Apr3);
%filename(2018-4-4, Apr4);
%filename(2018-4-5, Apr5);
%filename(2018-4-6, Apr6);
%filename(2018-4-7, Apr7);
%filename(2018-4-8, Apr8);
%filename(2018-4-9, Apr9);
%filename(2018-4-10, Apr10);
%filename(2018-4-11, Apr11);
%filename(2018-4-12, Apr12);
%filename(2018-4-13, Apr13);
%filename(2018-4-14, Apr14);
%filename(2018-4-15, Apr15);
%filename(2018-4-16, Apr16);
%filename(2018-4-17, Apr17);
%filename(2018-4-18, Apr18);
%filename(2018-4-19, Apr19);
%filename(2018-4-20, Apr20);
%filename(2018-4-21, Apr21);
%filename(2018-4-22, Apr22);
%filename(2018-4-23, Apr23);
%filename(2018-4-24, Apr24);
%filename(2018-4-25, Apr25);
%filename(2018-4-26, Apr26);
%filename(2018-4-27, Apr27);
%filename(2018-4-28, Apr28);
%filename(2018-4-29, Apr29);
%filename(2018-4-30, Apr30);
%filename(2018-5-1,May1) ;
%filename(2018-5-2,May2) ;
%filename(2018-5-3,May3) ;
%filename(2018-5-4,May4) ;
%filename(2018-5-5,May5) ;
%filename(2018-5-6,May6) ;
%filename(2018-5-7,May7) ;
%filename(2018-5-8,May8) ;
%filename(2018-5-9,May9) ;
%filename(2018-5-10,May10) ;
%filename(2018-5-11,May11) ;
%filename(2018-5-12,May12) ;
%filename(2018-5-13,May13) ;
%filename(2018-5-14,May14) ;
%filename(2018-5-15,May15) ;
%filename(2018-5-16,May16) ;
%filename(2018-5-17,May17) ;
%filename(2018-5-18,May18) ;
%filename(2018-5-19,May19) ;
%filename(2018-5-20,May20) ;
%filename(2018-5-21,May21) ;
%filename(2018-5-22,May22) ;
%filename(2018-5-23,May23) ;
%filename(2018-5-24,May24) ;
%filename(2018-5-25,May25) ;
%filename(2018-5-26,May26) ;
%filename(2018-5-27,May27) ;
%filename(2018-5-28,May28) ;
%filename(2018-5-29,May29) ;
%filename(2018-5-30,May30) ;
%filename(2018-5-31,May31) ;
%filename(2018-6-1,Jun1) ;
%filename(2018-6-2,Jun2) ;
%filename(2018-6-3,Jun3) ;
%filename(2018-6-4,Jun4) ;
%filename(2018-6-5,Jun5) ;
%filename(2018-6-6,Jun6) ;
%filename(2018-6-7,Jun7) ;
%filename(2018-6-8,Jun8) ;
%filename(2018-6-9,Jun9) ;
%filename(2018-6-10,Jun10) ;
%filename(2018-6-11,Jun11) ;
%filename(2018-6-12,Jun12) ;
%filename(2018-6-13,Jun13) ;
%filename(2018-6-14,Jun14) ;
%filename(2018-6-15,Jun15) ;
%filename(2018-6-16,Jun16) ;
%filename(2018-6-17,Jun17) ;
%filename(2018-6-18,Jun18) ;
%filename(2018-6-19,Jun19) ;
%filename(2018-6-20,Jun20) ;
%filename(2018-6-21,Jun21) ;
%filename(2018-6-22,Jun22) ;
%filename(2018-6-23,Jun23) ;
%filename(2018-6-24,Jun24) ;
%filename(2018-6-25,Jun25) ;
%filename(2018-6-26,Jun26) ;
%filename(2018-6-27,Jun27) ;
%filename(2018-6-28,Jun28) ;
%filename(2018-6-29,Jun29) ;
%filename(2018-6-30,Jun30) ;
%filename(2018-7-1,Jul1) ;
%filename(2018-7-2,Jul2) ;
%filename(2018-7-3,Jul3) ;
%filename(2018-7-4,Jul4) ;
%filename(2018-7-5,Jul5) ;
%filename(2018-7-6,Jul6) ;
%filename(2018-7-7,Jul7) ;
%filename(2018-7-8,Jul8) ;
%filename(2018-7-9,Jul9) ;
%filename(2018-7-10,Jul10) ;
%filename(2018-7-11,Jul11) ;
%filename(2018-7-12,Jul12) ;
%filename(2018-7-13,Jul13) ;
%filename(2018-7-14,Jul14) ;
%filename(2018-7-15,Jul15) ;
%filename(2018-7-16,Jul16) ;
%filename(2018-7-17,Jul17) ;
%filename(2018-7-18,Jul18) ;
%filename(2018-7-19,Jul19) ;
%filename(2018-7-20,Jul20) ;
%filename(2018-7-21,Jul21) ;
%filename(2018-7-22,Jul22) ;
%filename(2018-7-23,Jul23) ;
%filename(2018-7-24,Jul24) ;
%filename(2018-7-25,Jul25) ;
%filename(2018-7-26,Jul26) ;
%filename(2018-7-27,Jul27) ;
%filename(2018-7-28,Jul28) ;
%filename(2018-7-29,Jul29) ;
%filename(2018-7-30,Jul30) ;
%filename(2018-7-31,Jul31) ;
%filename(2018-8-1,Aug1) ;
%filename(2018-8-2,Aug2) ;
%filename(2018-8-3,Aug3) ;
%filename(2018-8-4,Aug4) ;
%filename(2018-8-5,Aug5) ;
%filename(2018-8-6,Aug6) ;
%filename(2018-8-7,Aug7) ;
%filename(2018-8-8,Aug8) ;
%filename(2018-8-9,Aug9) ;
%filename(2018-8-10,Aug10) ;
%filename(2018-8-11,Aug11) ;
%filename(2018-8-12,Aug12) ;
%filename(2018-8-13,Aug13) ;
%filename(2018-8-14,Aug14) ;
%filename(2018-8-15,Aug15) ;
%filename(2018-8-16,Aug16) ;
%filename(2018-8-17,Aug17) ;
%filename(2018-8-18,Aug18) ;
%filename(2018-8-19,Aug19) ;
%filename(2018-8-20,Aug20) ;
%filename(2018-8-21,Aug21) ;
%filename(2018-8-22,Aug22) ;
%filename(2018-8-23,Aug23) ;
%filename(2018-8-24,Aug24) ;
%filename(2018-8-25,Aug25) ;
%filename(2018-8-26,Aug26) ;
%filename(2018-8-27,Aug27) ;
%filename(2018-8-28,Aug28) ;
%filename(2018-8-29,Aug29) ;
%filename(2018-8-30,Aug30) ;
%filename(2018-8-31,Aug31) ;
%filename(2018-9-1,Sep1) ;
%filename(2018-9-2,Sep2) ;
%filename(2018-9-3,Sep3) ;
%filename(2018-9-4,Sep4) ;
%filename(2018-9-5,Sep5) ;
%filename(2018-9-6,Sep6) ;
%filename(2018-9-7,Sep7) ;
%filename(2018-9-8,Sep8) ;
%filename(2018-9-9,Sep9) ;
%filename(2018-9-10,Sep10) ;
%filename(2018-9-11,Sep11) ;
%filename(2018-9-12,Sep12) ;
%filename(2018-9-13,Sep13) ;
%filename(2018-9-14,Sep14) ;
%filename(2018-9-15,Sep15) ;
%filename(2018-9-16,Sep16) ;
%filename(2018-9-17,Sep17) ;
%filename(2018-9-18,Sep18) ;
%filename(2018-9-19,Sep19) ;
%filename(2018-9-20,Sep20) ;
%filename(2018-9-21,Sep21) ;
%filename(2018-9-22,Sep22) ;
%filename(2018-9-23,Sep23) ;
%filename(2018-9-24,Sep24) ;
%filename(2018-9-25,Sep25) ;
%filename(2018-9-26,Sep26) ;
%filename(2018-9-27,Sep27) ;
%filename(2018-9-28,Sep28) ;
%filename(2018-9-29,Sep29) ;
%filename(2018-9-30,Sep30) ;
%filename(2018-10-1,Oct1) ;
%filename(2018-10-2,Oct2) ;
%filename(2018-10-3,Oct3) ;
%filename(2018-10-4,Oct4) ;
%filename(2018-10-5,Oct5) ;
%filename(2018-10-6,Oct6) ;
%filename(2018-10-7,Oct7) ;
%filename(2018-10-8,Oct8) ;
%filename(2018-10-9,Oct9) ;
%filename(2018-10-10,Oct10) ;
%filename(2018-10-11,Oct11) ;
%filename(2018-10-12,Oct12) ;
%filename(2018-10-13,Oct13) ;
%filename(2018-10-14,Oct14) ;
%filename(2018-10-15,Oct15) ;
%filename(2018-10-16,Oct16) ;
%filename(2018-10-17,Oct17) ;
%filename(2018-10-18,Oct18) ;
%filename(2018-10-19,Oct19) ;
%filename(2018-10-20,Oct20) ;
%filename(2018-10-21,Oct21) ;
%filename(2018-10-22,Oct22) ;
%filename(2018-10-23,Oct23) ;
%filename(2018-10-24,Oct24) ;
%filename(2018-10-25,Oct25) ;
%filename(2018-10-26,Oct26) ;
%filename(2018-10-27,Oct27) ;
%filename(2018-10-28,Oct28) ;
%filename(2018-10-29,Oct29) ;
%filename(2018-10-30,Oct30) ;
%filename(2018-10-31,Oct31) ;
%filename(2018-11-1,Nov1) ;
%filename(2018-11-2,Nov2) ;
%filename(2018-11-3,Nov3) ;
%filename(2018-11-4,Nov4) ;
%filename(2018-11-5,Nov5) ;
%filename(2018-11-6,Nov6) ;
%filename(2018-11-7,Nov7) ;
%filename(2018-11-8,Nov8) ;
%filename(2018-11-9,Nov9) ;
%filename(2018-11-10,Nov10) ;
%filename(2018-11-11,Nov11) ;
%filename(2018-11-12,Nov12) ;
%filename(2018-11-13,Nov13) ;
%filename(2018-11-14,Nov14) ;
%filename(2018-11-15,Nov15) ;
%filename(2018-11-16,Nov16) ;
%filename(2018-11-17,Nov17) ;
%filename(2018-11-18,Nov18) ;
%filename(2018-11-19,Nov19) ;
%filename(2018-11-20,Nov20) ;
%filename(2018-11-21,Nov21) ;
%filename(2018-11-22,Nov22) ;
%filename(2018-11-23,Nov23) ;
%filename(2018-11-24,Nov24) ;
%filename(2018-11-25,Nov25) ;
%filename(2018-11-26,Nov26) ;
%filename(2018-11-27,Nov27) ;
%filename(2018-11-28,Nov28) ;
%filename(2018-11-29,Nov29) ;
%filename(2018-11-30,Nov30) ;
%filename(2018-12-1,Dec1) ;
%filename(2018-12-2,Dec2) ;
%filename(2018-12-3,Dec3) ;
%filename(2018-12-4,Dec4) ;
%filename(2018-12-5,Dec5) ;
%filename(2018-12-6,Dec6) ;
%filename(2018-12-7,Dec7) ;
%filename(2018-12-8,Dec8) ;
%filename(2018-12-9,Dec9) ;
%filename(2018-12-10,Dec10) ;
%filename(2018-12-11,Dec11) ;
%filename(2018-12-12,Dec12) ;
%filename(2018-12-13,Dec13) ;
%filename(2018-12-14,Dec14) ;
%filename(2018-12-15,Dec15) ;
%filename(2018-12-16,Dec16) ;
%filename(2018-12-17,Dec17) ;
%filename(2018-12-18,Dec18) ;
%filename(2018-12-19,Dec19) ;
%filename(2018-12-20,Dec20) ;
%filename(2018-12-21,Dec21) ;
%filename(2018-12-22,Dec22) ;
%filename(2018-12-23,Dec23) ;
%filename(2018-12-24,Dec24) ;
%filename(2018-12-25,Dec25) ;
%filename(2018-12-26,Dec26) ;
%filename(2018-12-27,Dec27) ;
%filename(2018-12-28,Dec28) ;
%filename(2018-12-29,Dec29) ;
%filename(2018-12-30,Dec30) ;
%filename(2018-12-31,Dec31) ;



proc freq data = stack_alldata3;
tables Has_Discount_by_Amazon ;
run;
/*****INTRODUCING SEASONALITY*****/
data stack_alldata11;
set stack_alldata1;
time1 = datepart(time);
format time1 mmddyy.;
season = 0;
run;
/****stacking all data -- for time being removing discount by amazon and product condition to include data period from Sep 13*****/
data stack_alldata22 ;
set stack_alldata2 ;
time1 = datepart(time);
format time1 mmddyy.;
season = 1;
run;
data stack_alldata33 (drop= Has_Discount_by_Amazon Amazon_Discount_Value) ;
set stack_alldata3 ;
time1 = datepart(time);
format time1 mmddyy.;
season = 1;
run;
data stack_alldata44 (drop= Condition Has_Discount_by_Amazon Amazon_Discount_Value) ;
set stack_alldata4 ;
time1 = datepart(time);
format time1 mmddyy.;
season = 1;
run;


data stack_alldata55 (drop= Condition Has_Discount_by_Amazon Amazon_Discount_Value) ;
set stack_alldata5 ;
time1 = datepart(time);
format time1 mmddyy8.;
if (time1 >= '01Jan2018'd and time1 <=  '31Jan2018'd) then season = 1;
else if (time1 >= '31Oct2018'd and time1 <= '31Dec2018'd) then season = 1;
else season = 0;
run;

/*proc contents data = stack_alldata33;run;*/
data stack_alldata;
set stack_alldata11 stack_alldata22 stack_alldata33 stack_alldata44 stack_alldata55;
run;
/**creating permanent SAS data ***/
data inn.stack_alldata;
set stack_alldata;
run;

proc freq data = stack_alldata;
tables season;
run;
/**extracting date and time from datetime ***/

data focal_rival_prods;
set stack_alldata;
/*where ASIN IN ('B00FLYWNYQ', 'B01B1VC13K', 'B01M0X67O0', 'B06VV9M11N', 'B01MYAJQES', 'B01NBC616L', 'B06XP79M4F', 'B00EZI26C8', 'B06XCMYN1H', 'B00YT6SY72', 'B003HF6PUO' , 'B01FIN77J0',*/
/*'B01MFEBQH1', 'B00N310CKG', 'B01NBKTPTS', 'B06Y1MP2PY', 'B01DBB96JU' , 'B000MPA044', 'B06VV9M11N', 'B013I40R8E' , 'B0051O92U4','B01NC15JKQ', 'B015TBCZ4I' , 'B004VEP2NQ', 'B00EU7C2BE',*/
/*'B010RWYPBK', 'B00VNML9OA', 'B01DEWZWDU', 'B00VNML9LI', 'B01DEWZWGC', 'B00KNUHBJU' , 'B01M7RABCO','B00NQ7QFGM','B007OZXVDM','B00ANEKTTA','B01JCECNM2','B005FYF7XQ','B000HEBAV2',*/
/*'B008GS8PS2');*/
/*length categ $50.;*/

where ASIN in ('B00FLYWNYQ', 'B01B1VC13K', 'B01M0X67O0', 'B06VV9M11N', 'B01MYAJQES', 'B01NBC616L', 'B06XP79M4F', 'B00EZI26C8', 'B06XCMYN1H', 'B00YT6SY72', 'B003HF6PUO' , 'B01FIN77J0',
'B01MFEBQH1', 'B00N310CKG', 'B01NBKTPTS', 'B06Y1MP2PY', 'B01DBB96JU' , 'B000MPA044', 'B06VV9M11N', 'B013I40R8E' , 'B0051O92U4') ;

/*then categ = 'Electric Cooker';*/

/*else if ASIN in ('B01NC15JKQ', 'B015TBCZ4I' , 'B004VEP2NQ', 'B00EU7C2BE', 'B010RWYPBK', 'B00VNML9OA', 'B01DEWZWDU', 'B00VNML9LI', 'B01DEWZWGC', 'B00KNUHBJU' , 'B01M7RABCO') */
/*then categ = 'Microwave';*/
/*else categ = 'Deep Fryer'; */

if ASIN in ('B00FLYWNYQ','B01B1VC13K', 'B01NC15JKQ' , 'B00NQ7QFGM' , 'B01MFEBQH1' ,'B00N310CKG' , 'B01NBKTPTS' , 'B06Y1MP2PY' , 'B01DBB96JU',
'B01M7RABCO') then do ;
focal_brand = 1;
nonfocal_brand1 = 0;  /**brand 1: Geek Chef**/
nonfocal_brand2 = 0; /*brand 2: Cosori*/
nonfocal_brand3 = 0; /**Oyama**/
nonfocal_brand4 = 0; /**Elechomes**/
nonfocal_brand5 = 0; /**Avalon Bay**/
nonfocal_brand6 = 0; /**Hamilton Beach**/
nonfocal_brand7 = 0; /**Cusimax**/
nonfocal_brand8 = 0; /**Pressure Pro**/
nonfocal_brand9 = 0; /**Crock-Pot**/
nonfocal_brand10 = 0; /**Magic Mill**/
nonfocal_brand11 = 0; /**Cuisinart**/
nonfocal_brand12 = 0; /**Breville**/
nonfocal_brand13 = 0; /**Elite Platinum**/
end;
else do;
if ASIN = 'B01M0X67O0' then do;
focal_brand = 0;
nonfocal_brand1 = 1; /**brand 1: Geek Chef**/
nonfocal_brand2 = 0; /*brand 2: Cosori*/
nonfocal_brand3 = 0; /**Oyama**/
nonfocal_brand4 = 0; /**Elechomes**/
nonfocal_brand5 = 0; /**Avalon Bay**/
nonfocal_brand6 = 0; /**Hamilton Beach**/
nonfocal_brand7 = 0; /**Cusimax**/
nonfocal_brand8 = 0; /**Pressure Pro**/
nonfocal_brand9 = 0; /**Crock-Pot**/
nonfocal_brand10 = 0; /**Magic Mill**/
nonfocal_brand11 = 0; /**Cuisinart**/
nonfocal_brand12 = 0; /**Breville**/
nonfocal_brand13 = 0; /**Elite Platinum**/
end;
if ASIN = 'B06VV9M11N' then do;
focal_brand = 0;
nonfocal_brand1 = 0; /**brand 1: Geek Chef**/
nonfocal_brand2 = 1; /*brand 2: Cosori*/
nonfocal_brand3 = 0; /**Oyama**/
nonfocal_brand4 = 0; /**Elechomes**/
nonfocal_brand5 = 0; /**Avalon Bay**/
nonfocal_brand6 = 0; /**Hamilton Beach**/
nonfocal_brand7 = 0; /**Cusimax**/
nonfocal_brand8 = 0; /**Pressure Pro**/
nonfocal_brand9 = 0; /**Crock-Pot**/
nonfocal_brand10 = 0; /**Magic Mill**/
nonfocal_brand11 = 0; /**Cuisinart**/
nonfocal_brand12 = 0; /**Breville**/
nonfocal_brand13 = 0; /**Elite Platinum**/
end;
if ASIN = 'B01MYAJQES' then do;
focal_brand = 0;
nonfocal_brand1 = 0; /**brand 1: Geek Chef**/
nonfocal_brand2 = 0; /*brand 2: Cosori*/
nonfocal_brand3 = 1; /**Oyama**/
nonfocal_brand4 = 0; /**Elechomes**/
nonfocal_brand5 = 0; /**Avalon Bay**/
nonfocal_brand6 = 0; /**Hamilton Beach**/
nonfocal_brand7 = 0; /**Cusimax**/
nonfocal_brand8 = 0; /**Pressure Pro**/
nonfocal_brand9 = 0; /**Crock-Pot**/
nonfocal_brand10 = 0; /**Magic Mill**/
nonfocal_brand11 = 0; /**Cuisinart**/
nonfocal_brand12 = 0; /**Breville**/
nonfocal_brand13 = 0; /**Elite Platinum**/
end;
if ASIN = 'B01NBC616L' then do;
focal_brand = 0;
nonfocal_brand1 = 0; /**brand 1: Geek Chef**/
nonfocal_brand2 = 0; /*brand 2: Cosori*/
nonfocal_brand3 = 0; /**Oyama**/
nonfocal_brand4 = 1; /**Elechomes**/
nonfocal_brand5 = 0; /**Avalon Bay**/
nonfocal_brand6 = 0; /**Hamilton Beach**/
nonfocal_brand7 = 0; /**Cusimax**/
nonfocal_brand8 = 0; /**Pressure Pro**/
nonfocal_brand9 = 0; /**Crock-Pot**/
nonfocal_brand10 = 0; /**Magic Mill**/
nonfocal_brand11 = 0; /**Cuisinart**/
nonfocal_brand12 = 0; /**Breville**/
nonfocal_brand13 = 0; /**Elite Platinum**/
end;
if ASIN = 'B06XP79M4F' then do;
focal_brand = 0;
nonfocal_brand1 = 0; /**brand 1: Geek Chef**/
nonfocal_brand2 = 0; /*brand 2: Cosori*/
nonfocal_brand3 = 0; /**Oyama**/
nonfocal_brand4 = 0; /**Elechomes**/
nonfocal_brand5 = 1; /**Avalon Bay**/
nonfocal_brand6 = 0; /**Hamilton Beach**/
nonfocal_brand7 = 0; /**Cusimax**/
nonfocal_brand8 = 0; /**Pressure Pro**/
nonfocal_brand9 = 0; /**Crock-Pot**/
nonfocal_brand10 = 0; /**Magic Mill**/
nonfocal_brand11 = 0; /**Cuisinart**/
nonfocal_brand12 = 0; /**Breville**/
nonfocal_brand13 = 0; /**Elite Platinum**/
end;
if ASIN = 'B00EZI26C8' then do;
focal_brand = 0;
nonfocal_brand1 = 0; /**brand 1: Geek Chef**/
nonfocal_brand2 = 0; /*brand 2: Cosori*/
nonfocal_brand3 = 0; /**Oyama**/
nonfocal_brand4 = 0; /**Elechomes**/
nonfocal_brand5 = 0; /**Avalon Bay**/
nonfocal_brand6 = 1; /**Hamilton Beach**/
nonfocal_brand7 = 0; /**Cusimax**/
nonfocal_brand8 = 0; /**Pressure Pro**/
nonfocal_brand9 = 0; /**Crock-Pot**/
nonfocal_brand10 = 0; /**Magic Mill**/
nonfocal_brand11 = 0; /**Cuisinart**/
nonfocal_brand12 = 0; /**Breville**/
nonfocal_brand13 = 0; /**Elite Platinum**/
end;
if ASIN = 'B06XCMYN1H' then do;
focal_brand = 0;
nonfocal_brand1 = 0; /**brand 1: Geek Chef**/
nonfocal_brand2 = 0; /*brand 2: Cosori*/
nonfocal_brand3 = 0; /**Oyama**/
nonfocal_brand4 = 0; /**Elechomes**/
nonfocal_brand5 = 0; /**Avalon Bay**/
nonfocal_brand6 = 0; /**Hamilton Beach**/
nonfocal_brand7 = 1; /**Cusimax**/
nonfocal_brand8 = 0; /**Pressure Pro**/
nonfocal_brand9 = 0; /**Crock-Pot**/
nonfocal_brand10 = 0; /**Magic Mill**/
nonfocal_brand11 = 0; /**Cuisinart**/
nonfocal_brand12 = 0; /**Breville**/
nonfocal_brand13 = 0; /**Elite Platinum**/
end;
if ASIN = 'B00YT6SY72' then do;
focal_brand = 0;
nonfocal_brand1 = 0; /**brand 1: Geek Chef**/
nonfocal_brand2 = 0; /*brand 2: Cosori*/
nonfocal_brand3 = 0; /**Oyama**/
nonfocal_brand4 = 0; /**Elechomes**/
nonfocal_brand5 = 0; /**Avalon Bay**/
nonfocal_brand6 = 0; /**Hamilton Beach**/
nonfocal_brand7 = 0; /**Cusimax**/
nonfocal_brand8 = 1; /**Pressure Pro**/
nonfocal_brand9 = 0; /**Crock-Pot**/
nonfocal_brand10 = 0; /**Magic Mill**/
nonfocal_brand11 = 0; /**Cuisinart**/
nonfocal_brand12 = 0; /**Breville**/
nonfocal_brand13 = 0; /**Elite Platinum**/
end;
if ASIN = 'B003HF6PUO' then do;
focal_brand = 0;
nonfocal_brand1 = 0; /**brand 1: Geek Chef**/
nonfocal_brand2 = 0; /*brand 2: Cosori*/
nonfocal_brand3 = 0; /**Oyama**/
nonfocal_brand4 = 0; /**Elechomes**/
nonfocal_brand5 = 0; /**Avalon Bay**/
nonfocal_brand6 = 0; /**Hamilton Beach**/
nonfocal_brand7 = 0; /**Cusimax**/
nonfocal_brand8 = 0; /**Pressure Pro**/
nonfocal_brand9 = 1; /**Crock-Pot**/
nonfocal_brand10 = 0; /**Magic Mill**/
nonfocal_brand11 = 0; /**Cuisinart**/
nonfocal_brand12 = 0; /**Breville**/
nonfocal_brand13 = 0; /**Elite Platinum**/
end;
if ASIN = 'B01FIN77J0' then do;
focal_brand = 0;
nonfocal_brand1 = 0; /**brand 1: Geek Chef**/
nonfocal_brand2 = 0; /*brand 2: Cosori*/
nonfocal_brand3 = 0; /**Oyama**/
nonfocal_brand4 = 0; /**Elechomes**/
nonfocal_brand5 = 0; /**Avalon Bay**/
nonfocal_brand6 = 0; /**Hamilton Beach**/
nonfocal_brand7 = 0; /**Cusimax**/
nonfocal_brand8 = 0; /**Pressure Pro**/
nonfocal_brand9 = 0; /**Crock-Pot**/
nonfocal_brand10 = 1; /**Magic Mill**/
nonfocal_brand11 = 0; /**Cuisinart**/
nonfocal_brand12 = 0; /**Breville**/
nonfocal_brand13 = 0; /**Elite Platinum**/
end;
if ASIN = 'B000MPA044' then do;
focal_brand = 0;
nonfocal_brand1 = 0; /**brand 1: Geek Chef**/
nonfocal_brand2 = 0; /*brand 2: Cosori*/
nonfocal_brand3 = 0; /**Oyama**/
nonfocal_brand4 = 0; /**Elechomes**/
nonfocal_brand5 = 0; /**Avalon Bay**/
nonfocal_brand6 = 0; /**Hamilton Beach**/
nonfocal_brand7 = 0; /**Cusimax**/
nonfocal_brand8 = 0; /**Pressure Pro**/
nonfocal_brand9 = 0; /**Crock-Pot**/
nonfocal_brand10 = 0; /**Magic Mill**/
nonfocal_brand11 = 1; /**Cuisinart**/
nonfocal_brand12 = 0; /**Breville**/
nonfocal_brand13 = 0; /**Elite Platinum**/
end;
if ASIN = 'B013I40R8E' then do;
focal_brand = 0;
nonfocal_brand1 = 0; /**brand 1: Geek Chef**/
nonfocal_brand2 = 0; /*brand 2: Cosori*/
nonfocal_brand3 = 0; /**Oyama**/
nonfocal_brand4 = 0; /**Elechomes**/
nonfocal_brand5 = 0; /**Avalon Bay**/
nonfocal_brand6 = 0; /**Hamilton Beach**/
nonfocal_brand7 = 0; /**Cusimax**/
nonfocal_brand8 = 0; /**Pressure Pro**/
nonfocal_brand9 = 0; /**Crock-Pot**/
nonfocal_brand10 = 0; /**Magic Mill**/
nonfocal_brand11 = 0; /**Cuisinart**/
nonfocal_brand12 = 1; /**Breville**/
nonfocal_brand13 = 0; /**Elite Platinum**/
end;
if ASIN = 'B0051O92U4' then do;
focal_brand = 0;
nonfocal_brand1 = 0; /**brand 1: Geek Chef**/
nonfocal_brand2 = 0; /*brand 2: Cosori*/
nonfocal_brand3 = 0; /**Oyama**/
nonfocal_brand4 = 0; /**Elechomes**/
nonfocal_brand5 = 0; /**Avalon Bay**/
nonfocal_brand6 = 0; /**Hamilton Beach**/
nonfocal_brand7 = 0; /**Cusimax**/
nonfocal_brand8 = 0; /**Pressure Pro**/
nonfocal_brand9 = 0; /**Crock-Pot**/
nonfocal_brand10 = 0; /**Magic Mill**/
nonfocal_brand11 = 0; /**Cuisinart**/
nonfocal_brand12 = 0; /**Breville**/
nonfocal_brand13 = 1; /**Elite Platinum**/
end;
end;
run;

/*proc contents data = focal_rival_prods ;run;*/
proc print data = focal_rival_prods (obs=30);
where Fulfilled_by_Amazon__Y_N_ = 'False' and Amazon_Prime__Y_N_ = 'True';
run;
/*proc PRINT data = focal_rival_prods (OBS=10);*/
/*VAR Buy_Box_Price ;*/
/*WHERE SUBSTR(Buy_Box_Price,1,1)='$';*/
/*run;*/
/*PROC CONTENTS DATA = focal_rival_prods;*/
/*RUN;*/

data focal_rival_prods1 ;
set focal_rival_prods;
where SUBSTR(price,1,1)= '$' and SUBSTR(Buy_Box_Price,1,1)= '$';
if Amazon_Prime__Y_N_ = 'ERROR' then Amazon_Prime__Y_N_ = 'False';
run;
proc freq data = focal_rival_prods1 ;
tables Amazon_Prime__Y_N_;
run;
proc print data = focal_rival_prods1 (obs=10);
where Amazon_Prime__Y_N_ = 'ERROR';
run;
proc contents data = focal_rival_prods1;
run;



data focal_rival_prods2;
set focal_rival_prods1;
price1 = input(price, dollar10.);
Buy_Box_Price1 = input(Buy_Box_Price, dollar10.);
seller_rate = input(Seller_Rating, comma6.);
num_seller_Posrating = input(__Positive_Ratings, comma6.)/100;
num_seller_ratings = input(__Ratings, comma6.);
Product_Star_Rate = input(Product_Star_Rating,comma6.);
Sales_Rank_Own_Categ = input(Sales_Rank_in_Own_Category, comma6.);
Sales_Rank_Sub_categ = input(Sales_Rank_in_Sub__Category, comma6.);

if __Answered_Questions = '1000+' then Answered_Questions = 1000 ; 
else IF SUBSTR(__Answered_Questions,1,1) = 'E' THEN Answered_Questions=0;
ELSE Answered_Questions = input(__Answered_Questions, comma6.);

Product_reviews = input(__Customer_Reviews_on_Product, comma6.);
if Condition___New = 'True' then condition = 1; else condition = 0;
if Free_Shipping__Y_N_ = 'FREE Shipping' then Freeship = 1; else Freeship = 0;
if Fulfilled_by_Amazon__Y_N_ = 'True' then FBA = 1; else FBA = 0;
if Amazon_Prime__Y_N_ = 'True' then Prime = 1 ; else Prime = 0;
if  In_Stock__Indicator_Y_N_NA_varia = 'True' then Instock = 1; else Instock = 0;
run;

data focal_rival_prods3 (keep = time focal_brand nonfocal_brand1 nonfocal_brand2 nonfocal_brand3 nonfocal_brand4 nonfocal_brand5 nonfocal_brand6 nonfocal_brand7 nonfocal_brand8 
nonfocal_brand9 nonfocal_brand10 nonfocal_brand11 nonfocal_brand12 nonfocal_brand13 ASIN seller_ID price1 Buy_Box_Price1 seller_rate 
num_seller_Posrating  num_seller_ratings Product_Star_Rate Sales_Rank_Own_Categ Sales_Rank_Sub_categ Answered_Questions Product_reviews condition Freeship  FBA Prime Instock season);
set focal_rival_prods2;
if Answered_Questions = . then Answered_Questions = 0;
run;

/****RETAINING DAILY DATA****/

data focal_rival_prods22;
set focal_rival_prods3;
time1 = datepart(time);
format time1 mmddyy.;
run;

/*proc print data = focal_rival_prods22;*/
/*where Seller_ID = 'Amazon' and ASIN = 'B00FLYWNYQ';*/
/*run;*/

PROC SORT DATA = focal_rival_prods22 NODUPKEY OUT= focal_rival_prods23;
BY TIME1 ASIN Seller_ID condition ;
RUN;
/*proc freq data = focal_rival_prods23 ;*/
/*tables ASIN;*/
/*where seller_ID in ('AKAOZOL2YUU3N','AO7W77Q0T38ZF', 'AS4010PD8JYCN', 'AU5C366YYMKXX');*/
/*run;*/
/*PROC EXPORT */
/*DATA=focal_rival_prods23*/
/*DBMS=csv*/
/*OUTFILE= "C:\Dynamic Pricing\Data\Data Cleaning and Summary Statistics\Home and Kitchen\cooker_rawdata_11-6.csv"*/
/*REPLACE;*/
/*run;*/
/*PROC EXPORT */
/*DATA=focal_rival_prods23*/
/*DBMS=csv*/
/*OUTFILE= "C:\Dynamic Pricing\Data\Data Cleaning and Summary Statistics\Home and Kitchen\cooker_rawdata_2-11.csv"*/
/*REPLACE;*/
/*run;*/
/*PROC EXPORT */
/*DATA=focal_rival_prods23*/
/*DBMS=csv*/
/*OUTFILE= "C:\Dynamic Pricing\Data\Data Cleaning and Summary Statistics\Home and Kitchen\cooker_rawdata_3-19.csv"*/
/*REPLACE;*/
/*run;*/
data inn.focal_rival_prods23;
set focal_rival_prods23;
run;
PROC EXPORT 
DATA=focal_rival_prods23
DBMS=csv
OUTFILE= "C:\Dynamic Pricing\Data Scraped\Home_Kitchen\cooker_rawdailydata_sep17_dec18.csv"
REPLACE;
run;
proc print data = focal_rival_prods23 (obs=20);
var time1;
run;

/*proc freq data = price_trend;*/
/*tables time1;*/
/*run;*/
/****CLUSTERING OLD ITEM SELLERS UPFRONT****/
data focal_rival_prods24;
set focal_rival_prods23;
/*set ELECTRICCOOKER_BRANDCHARACS;*/
length seller_type $50.;
IF CONDITION = 0 AND SELLER_ID = 'Amazon' AND focal_brand = 1 THEN SELLER_TYPE = 'Amazon Old Focal';
ELSE IF CONDITION = 0 AND SELLER_ID = 'Amazon' AND focal_brand = 0 THEN SELLER_TYPE = 'Amazon Old Non-Focal';
ELSE IF CONDITION = 1 AND SELLER_ID = 'Amazon' THEN SELLER_TYPE = 'Amazon New';
ELSE IF CONDITION = 0 AND SELLER_ID NE 'Amazon' AND focal_brand = 1 THEN SELLER_TYPE = '3P Old Focal';
ELSE IF CONDITION = 0 AND SELLER_ID NE 'Amazon' AND focal_brand = 0 THEN SELLER_TYPE = '3P Old Non-Focal';
ELSE IF CONDITION = 1 AND SELLER_ID NE 'Amazon' THEN SELLER_TYPE = '3P New';
run;
/*proc freq data = focal_rival_prods24;*/
/*tables seller_ID;*/
/*where SELLER_TYPE = '3P Old Focal' ;*/
/*run;*/
proc contents data = ELECTRICCOOKER_BRANDCHARACS;
run;
/***TABLE 2 CREATION**/
DATA ELECTRICOOKER_BRAND;
SET focal_rival_prods24;
length brand $30.;
if focal_brand = 1 then brand = 'Instant Pot';
if	nonfocal_brand1	=	1	then brand = 	'Geek Chef';
if	nonfocal_brand2	=	1	then brand = 	'Cosori';
if	nonfocal_brand3	=	1	then brand = 	'Oyama';
if	nonfocal_brand4	=	1	then brand = 	'Elechomes';
if	nonfocal_brand5	=	1	then brand = 	'Avalon Bay';
if	nonfocal_brand6	=	1	then brand = 	'Hamilton Beach';
if	nonfocal_brand7	=	1	then brand = 	'Cusimax';
if	nonfocal_brand8	=	1	then brand = 	'Pressure Pro';
if	nonfocal_brand9	=	1	then brand = 	'Crock-Pot';
if	nonfocal_brand10	=	1	then brand = 	'Magic Mill';
if	nonfocal_brand11	=	1	then brand = 	'Cuisinart';
if	nonfocal_brand12	=	1	then brand = 	'Breville';
if	nonfocal_brand13	=	1	then brand = 	'Elite Platinum';
run;
proc sql;
create table distinctsellers as
select 
brand,
seller_type,
count(distinct seller_ID) as num_sellers
from 
ELECTRICOOKER_BRAND
where condition = 1
group by
brand,
seller_type;
quit;
proc sql;
create table distinctsellers1 as
select 
seller_type,
count(distinct seller_ID) as num_sellers
from 
ELECTRICOOKER_BRAND
where condition = 1
group by
seller_type;
quit;
/***Table 2: brand characteristics summary***/
proc means data = ELECTRICOOKER_BRAND n mean nway ;
class brand;
var Buy_Box_Price1 Answered_Questions Product_Star_Rate Product_reviews Sales_Rank_Own_Categ;
output out = summ_brand mean=;
run;



proc sql;
create table count_all as
select
count(distinct seller_ID) as num_sellers,
count(distinct time1) as num_days
from 
ELECTRICOOKER_BRAND
where 
seller_type in ( '3P New', 'Amazon New');
quit;

proc sql;
create table count_dist as
select
ASIN,
count(distinct seller_ID) as num_sellers
from 
ELECTRICOOKER_BRAND
where 
seller_type in ( '3P New', 'Amazon New')
group by
ASIN;
quit;

proc sql;
create table count_dist_sellerpresence as
select
ASIN,
seller_ID,
count(distinct seller_ID) as num_sellers,
count(distinct time1) as num_days
from 
ELECTRICOOKER_BRAND
where 
seller_type in ( '3P New', 'Amazon New')
group by
ASIN,
seller_ID;
quit;

proc sql;
create table price_trend_asin_seller as
select
time1,
ASIN,
seller_ID,
price1 as price
from 
ELECTRICOOKER_BRAND
where 
seller_type in ( '3P New', 'Amazon New')
group by
ASIN,
seller_ID;
quit;

PROC EXPORT 
DATA=count_all
DBMS=csv
OUTFILE= "C:\Dynamic Pricing\Data Scraped\Home_Kitchen\Data Analysis\CountNewsellersanddays.csv"
REPLACE;
run;


PROC EXPORT 
DATA=count_dist
DBMS=csv
OUTFILE= "C:\Dynamic Pricing\Data Scraped\Home_Kitchen\Data Analysis\CountNewsellers_bYASIN.csv"
REPLACE;
run;


PROC EXPORT 
DATA=count_dist_sellerpresence
DBMS=csv
OUTFILE= "C:\Dynamic Pricing\Data Scraped\Home_Kitchen\Data Analysis\CountNewsellersanddayspresent_bYASIN.csv"
REPLACE;
run;
PROC EXPORT 
DATA=price_trend_asin_seller
DBMS=csv
OUTFILE= "C:\Dynamic Pricing\Data Scraped\Home_Kitchen\Data Analysis\price_trend_asin_seller.csv"
REPLACE;
run;
/****UNIVARIATE ON SELLER DAYS****/
PROC UNIVARIATE DATA = count_dist_sellerpresence;
VAR num_days;
BY asin;
RUN;
/****REMOVING SELLERS WITH < 20 DAYS ON THE MARKETPLACE***/

DATA REDUCED_SELLERS;
SET count_dist_sellerpresence;
WHERE num_days > 20;
RUN;
PROC FREQ DATA = REDUCED_SELLERs;
TABLES NUM_SELLERS*ASIN /list ;
run;
proc sort data = REDUCED_SELLERS (keep = ASIN num_sellers seller_ID) nodupkey out = reduced_seller_list;
by ASIN seller_ID;
run;
proc sort data = ELECTRICOOKER_BRAND out = focal_rival_prods25;
by ASIN seller_ID;
run;
data focal_rival_prods26;
merge focal_rival_prods25 (in=a) reduced_seller_list (in=b);
by ASIN seller_ID;
if a and b;
run;
proc sort data = focal_rival_prods26;
by time1;
run;

proc contents data = focal_rival_prods26;
run;

proc sql;
create table count_sellers_reduced as
select
ASIN,
count(distinct seller_ID) as num_sellers
from 
focal_rival_prods26
where 
seller_type in ( '3P New', 'Amazon New')
group by
ASIN;
quit;

/*proc print data = count_sellers_reduced ;*/
/*var seller_ID;*/
/*where ASIN = 'B000MPA044';*/
/*run;*/
/*proc print data = REDUCED_SELLERS ;*/
/*var seller_ID;*/
/*where ASIN = 'B000MPA044';*/
/*run;*/


/***DETERMINING SELLER CHARACTERISTICS FOR EACH SELLER (Table 3)***/

proc sql;
create table SELLER_VARS as
select 
Seller_ID,
SELLER_TYPE,
count(distinct time1) as num_days,
count(ASIN) as num_ASIN_sold,
count(distinct ASIN) as num_uniq_ASIN_sold,
mean(condition) as mean_condition,
mean(freeship) as mean_freeship,
mean(FBA) as mean_FBA,
mean(Prime) as mean_prime,
mean(focal_brand) as focal,
mean(nonfocal_brand1) as nonfocal1,
mean(nonfocal_brand2) as nonfocal2,
mean(nonfocal_brand3) as nonfocal3,
mean(nonfocal_brand4) as nonfocal4,
mean(nonfocal_brand5) as nonfocal5,
mean(nonfocal_brand6) as nonfocal6,
mean(nonfocal_brand7) as nonfocal7,
mean(nonfocal_brand8) as nonfocal8,
mean(nonfocal_brand9) as nonfocal9,
mean(nonfocal_brand10) as nonfocal10,
mean(nonfocal_brand11) as nonfocal11,
mean(nonfocal_brand12) as nonfocal12,
mean(nonfocal_brand13) as nonfocal13,
MEAN(seller_rate) as MEAN_SELLER_RATE,
min(seller_rate) as Min_SELLER_RATE,
max(seller_rate) as Max_SELLER_RATE,
MAX(num_seller_Posrating) AS MAX_PERC_POSRATING,
Min(num_seller_Posrating) AS Min_PERC_POSRATING,
Mean(num_seller_Posrating) AS Mean_PERC_POSRATING,
MAX(num_seller_ratings) AS MAX_NUM_RATINGS,
Min(num_seller_ratings) AS Min_NUM_RATINGS,
Mean(num_seller_ratings) AS Mean_NUM_RATINGS,
MEAN(price1) as MEAN_PRICE,
Max(price1) as Max_PRICE,
Min(price1) as Min_PRICE
from 
focal_rival_prods26
group by
Seller_ID,
SELLER_TYPE
;
quit;
/**TABLE 3***/

proc means data = SELLER_VARS n mean max min std nway;
class seller_type;
var mean_FBA mean_freeship Mean_PERC_POSRATING MEAN_SELLER_RATE MAX_NUM_RATINGS num_uniq_ASIN_sold num_days;
output out = table3_summary ;
run;
/**COMPUTING PRICE CHANGE BY ASIN AND SELLER TYPE***/

proc sort data = focal_rival_prods26 NODUPREC out=SELLER_TYPE_TREND1 ;
by  ASIN seller_type seller_ID TIME1 ;
run;
proc expand data=SELLER_TYPE_TREND1 out=SELLER_TYPE_TREND12 method = none; 
  by ASIN seller_type seller_ID;
  convert price1 = price_lag1     / transformout=(lag 1); 
RUN;

proc sql;
create table price_changes_sellertype as
select 
time1,
ASIN,
seller_type,
seller_ID,
price1,
(case when price_lag1 ne . and price_lag1 ne 0 then (price1-price_lag1)/price_lag1 else 0 end) as pricechange

from
SELLER_TYPE_TREND12
group by
time1,
ASIN,
seller_type,
seller_ID;
quit;
proc print data = price_changes_sellertype (obs=10);
run;

proc sql;
create table numpricechange_bysellertype as
select
ASIN,
seller_ID,
seller_type,
sum(case when pricechange ne 0 then 1 else 0 end) as num_pricechanges
from price_changes_sellertype
group by
ASIN,
seller_ID, 
seller_type;
quit;
proc means data = numpricechange_bysellertype n mean max min std nway;
class seller_type;
var num_pricechanges;
output out = table3_summary2;
run;

proc means data = numpricechange_bysellertype sum  nway;
class seller_id;
var num_pricechanges;
output out = price_change_freq sum=;
run;

/***counting number of sellers by ASINs*****/
proc sql;
create table num_sellers_byasin as
select 
ASIN,
count(distinct Seller_ID) as num_sellers
from 
focal_rival_prods26
where seller_type = '3P New'
group by
ASIN
;
quit;

proc print data = SELLER_VARS ;
var seller_ID; 
where SELLER_TYPE = '3P New' and mean_condition < 1; 
run;

/*proc freq data = SELLER_VARS ;*/
/*tables seller_type ;*/
/*where seller_ID = 'A06539733L3EU9';*/
/*where seller_type in ('3P New', '3P Old Focal', '3P Old Non-Focal');*/
/*run;*/

proc sql;
create table price_dev_BB as
select 
time,
ASIN,
Seller_ID,
(price1-Buy_Box_Price1)as dev_BBprice

from 
focal_rival_prods26
group by
time,
ASIN,
Seller_ID
;
quit;
/***ADDED VARIABLES AFTER CONVO WITH BRYAN --- NEED TO INCLUDE THESE IN THE SELLER ENTRY AND BUY BOX MODELS***/
/**SELLER ENTRY: NON BUY BOX 3P PRICE; BUY BOX MODEL: DIFFERENCE FROM LOWEST PRICE OFFER***/
proc sql;
   create table lowestprice_offer as
   select 
      time1,
      ASIN,
/*      Buy_Box_Price1,*/
      min(price1) as lowestprice_offer
   from focal_rival_prods26
   group by time1, ASIN;
quit;

data non_buybox3pprice;
set focal_rival_prods26;
where seller_id ne 'Amazon' and price1 ne Buy_Box_Price1;
run;
proc sql;
   create table mean_nonbuybox3pprice as
   select 
      time1,
      ASIN,
      mean(price1) as nonbuybox3p_price
   from non_buybox3pprice
   group by time1, ASIN;
quit;
proc print data = mean_nonbuybox3pprice (obs=20);
run;


/***DETERMINING MEAN deviations from BB price*****/

proc sql;
create table meanstd_dev_BUYBOXPRICE as
select 
Seller_ID,
MEAN(dev_BBprice) as MEANdev_BUYBOXPRICE,
SQRT(VAR(dev_BBprice)) as stddev_BUYBOXPRICE
from 
price_dev_BB
group by
Seller_ID
;
quit;
/**ACCOUNTING FOR THE FACT THAT SOME SELLERS SELL ONLY 1 ASIN IN A CATEG, SO STD OF PRICE DEV WILL BE 0**/
DATA meanstd_dev_BUYBOXPRICE1;
SET meanstd_dev_BUYBOXPRICE;
IF stddev_BUYBOXPRICE = . THEN stddev_BUYBOXPRICE = 0;
RUN;
proc print data = meanstd_dev_BUYBOXPRICE1 (obs=20);
run;
/**MERGING BACK TO THE SELLER VARS DATASET**/

DATA SELLER_VARS1;
MERGE SELLER_VARS (IN=A) meanstd_dev_BUYBOXPRICE1 (IN=B);
BY Seller_ID;
if a and b;
RUN;

/***EXTRACTING THIRD PARTY SELLERS FOR CATEGORY = 'Electric Cooker' ****/

DATA SELLER_VARS2_Cooker;
SET SELLER_VARS1;
WHERE SELLER_TYPE = '3P New';
RUN;


DATA SELLER_VARS3_Cooker;
SET SELLER_VARS1;
WHERE SELLER_TYPE ne '3P New';
RUN;
/**/
/*PROC PRINT DATA = SELLER_VARS2_Cooker (OBS=20);*/
/*RUN;*/
/**TREATING EXTREME OBSERVATIONS***/
/**DROPPING SELLERS WITH <= 10 MAX_NUM_RATINGS, MISSING MAX_PERC_POSRATING, MISSING MEAN_SELLER_RATE, <= 10 num_ASIN_sold***/
/*DATA SELLER_VARS2_Cooker1;*/
/*SET SELLER_VARS2_Cooker;*/
/*WHERE MAX_PERC_POSRATING NE . AND MEAN_SELLER_RATE NE . ;*/
/*run;*/
/*proc contents data = SELLER_VARS2_Cooker1; run;*/
/*****STANDARDINZING DATASET FOR CLUSTER ANALYSIS ONLY FOR 3P NEW***/
proc standard data = SELLER_VARS2_Cooker  mean=0 std=1 out= SELLER_standvars_Cooker;
VAR 
MAX_NUM_RATINGS
MAX_PERC_POSRATING
MEAN_PRICE
MEAN_SELLER_RATE
MEANdev_BUYBOXPRICE
Max_PRICE
Max_SELLER_RATE
Mean_NUM_RATINGS
Mean_PERC_POSRATING
Min_NUM_RATINGS
Min_PERC_POSRATING
Min_PRICE
Min_SELLER_RATE
mean_FBA
mean_prime
mean_condition
mean_freeship
num_ASIN_sold
num_uniq_ASIN_sold
stddev_BUYBOXPRICE
;
RUN;
/*PROC PRINT DATA = SELLER_standvars_Cooker (obs=20);*/
/*RUN;*/

/**RUNNING VARCLUS PROCEDURE TO REMOVE CORRELATED VARIABLES**/

proc varclus data=SELLER_standvars_Cooker    outtree=tree centroid MAXITER = 1000 maxclusters= 40  CENTROID;
var 
/*MAX_NUM_RATINGS*/
/*MAX_PERC_POSRATING*/
/*MEAN_SELLER_RATE*/
/*Max_SELLER_RATE*/
Mean_NUM_RATINGS
Mean_PERC_POSRATING
/*Min_NUM_RATINGS*/
/*Min_PERC_POSRATING*/
/*Min_SELLER_RATE*/
mean_FBA
mean_prime
/*mean_condition*/
mean_freeship
num_ASIN_sold
/*num_uniq_ASIN_sold*/
/*stddev_BUYBOXPRICE*/
;
run;
/*proc freq data = SELLER_standvars_Cooker;*/
/*tables seller_id;*/
/*run;*/
/**RUNNING FASTCLUS PROCEDURE***/

proc fastclus data = SELLER_standvars_Cooker
 out= sellers_clusters_Cooker
 maxc= 10 converge = 0 maxiter=100000 summary;
 var 
Mean_NUM_RATINGS
/*Mean_PERC_POSRATING*/
/*focal*/
mean_FBA
/*mean_prime*/
mean_freeship
/*nonfocal1*/
/*nonfocal2*/
/*nonfocal3*/
/*nonfocal4*/
/*nonfocal5*/
/*nonfocal6*/
/*nonfocal7*/
/*nonfocal8*/
/*nonfocal9*/
/*nonfocal10*/
/*nonfocal11*/
/*nonfocal12*/
/*nonfocal13*/
num_ASIN_sold
;
run;


PROC EXPORT 
DATA=sellers_clusters_Cooker
DBMS=csv
OUTFILE= "C:\Dynamic Pricing\Data Scraped\Home_Kitchen\Cluster Analysis\clusteroutput_Feb2020.csv"
REPLACE;
run;

/****CLUBBING CLOSE CLUSTERS TO GET FINAL SET OF CLUSTERS****/
/****with mean_condition as the clustering variable*****/
/**/


DATA sellers_clusters_Cooker1;
SET sellers_clusters_Cooker;
IF CLUSTER IN (1,8)  THEN CLUSTER1 = 1;
ELSE IF CLUSTER IN (4,7) THEN CLUSTER1 = 3;
ELSE IF CLUSTER IN (5,10) THEN CLUSTER1 = 4;
ELSE IF CLUSTER IN (6, 9) THEN CLUSTER1 = 5;
ELSE CLUSTER1 = CLUSTER;
RUN;

PROC FREQ DATA = sellers_clusters_Cooker1;
TABLES CLUSTER1;
RUN;
/***COMPUTING CLUSTER CENTROIDS****/
PROC MEANS DATA = sellers_clusters_Cooker1 N SUM NWAY;
CLASS CLUSTER1;
VAR Mean_NUM_RATINGS mean_FBA mean_freeship num_ASIN_sold;
OUTPUT OUT = CLUSTER_CENTROIDS MEAN=;
RUN;


/*PROC PRINT DATA = sellers_clusters_Cooker;*/
/*RUN;*/
/***MERGING CLUSTERS BACK TO THE UN-STANDARDIZED DATA SET TO RECOVER MEAN CLUSTER CHARACTERISTICS****/

PROC SORT DATA = sellers_clusters_Cooker1 (KEEP= Seller_ID Cluster1 ) OUT=sellers_clusters_Cooker_SORT;
BY Seller_ID;
RUN;

PROC SORT DATA = SELLER_VARS2_Cooker out=SELLER_VARS2_Cooker_sort;
BY Seller_ID;
RUN;
PROC SORT DATA = SELLER_VARS3_Cooker out=SELLER_VARS3_Cooker_sort;
BY Seller_ID;
RUN;
data seller_cooker_clusters (drop= cluster1);
merge sellers_clusters_Cooker_SORT (in=a) SELLER_VARS2_Cooker_sort (in=b);
BY Seller_ID;
if a and b;
IF CLUSTER1 = 1 THEN seller_type = 'Cluster 1';
if CLUSTER1 = 2 THEN seller_type = 'Cluster 2';
if CLUSTER1 = 3 THEN seller_type = 'Cluster 3';
if CLUSTER1 = 4 THEN seller_type = 'Cluster 4';
if CLUSTER1 = 5 THEN seller_type = 'Cluster 5';
/*if CLUSTER1 = 6 THEN seller_type = 'Cluster 6';*/
run;

data seller_cooker_clusters_all;
set seller_cooker_clusters SELLER_VARS3_Cooker_sort;
run;

proc means data = seller_cooker_clusters_all n mean min max std nway noprint;
class seller_type;
var
MAX_NUM_RATINGS
MAX_PERC_POSRATING
MEAN_SELLER_RATE
Max_SELLER_RATE
Mean_NUM_RATINGS
Mean_PERC_POSRATING
Min_NUM_RATINGS
Min_PERC_POSRATING
Min_PRICE
Min_SELLER_RATE
focal
mean_FBA
mean_prime
mean_condition
mean_freeship
nonfocal1
nonfocal2
nonfocal3
nonfocal4
nonfocal5
nonfocal6
nonfocal7
nonfocal8
nonfocal9
nonfocal10
nonfocal11
nonfocal12
nonfocal13
num_ASIN_sold
num_uniq_ASIN_sold
MEANdev_BUYBOXPRICE
stddev_BUYBOXPRICE;
output out= seller_cooker_cluster_charac ;
run;

PROC EXPORT 
DATA=seller_cooker_cluster_charac
DBMS=csv
OUTFILE= "C:\Dynamic Pricing\Data Scraped\Home_Kitchen\Cluster Analysis\clusterPROFILEs_Feb2020.csv"
REPLACE;
run;

proc print data = seller_cooker_cluster_charac ;
run;

/******MERGING CLUSTER OUTPUT WITH THE DAILY PRICE TREND DATA AT ASIN LEVEL***/

/***adding the mean non buy box price for the seller entry model*****/
proc sort data = mean_nonbuybox3pprice;
by time1 ASIN;
run;
proc sort data = focal_rival_prods26 out = focal_rival_prods26_sort;
by time1 ASIN;
run;

data focal_rival_prods27;
merge focal_rival_prods26_sort (in=a) mean_nonbuybox3pprice (in=b);
by time1 ASIN;
if a and b;
run;
proc contents data = focal_rival_prods27;
run;

proc means data = focal_rival_prods27 n sum nway noprint;
class time1 focal_brand nonfocal_brand1 nonfocal_brand2 nonfocal_brand3 nonfocal_brand4 nonfocal_brand5 nonfocal_brand6 nonfocal_brand7 nonfocal_brand8 
nonfocal_brand9 nonfocal_brand10 nonfocal_brand11 nonfocal_brand12 nonfocal_brand13 ASIN seller_id seller_type;
var price1 Buy_Box_Price1 nonbuybox3p_price seller_rate num_seller_Posrating  num_seller_ratings condition Freeship  FBA Prime Instock ;
output out = price_trend sum= ;
run;
proc print data = price_trend (obs=10);
run;

PROC SORT DATA = price_trend OUT= price_cooker_trend1;
BY SELLER_ID ;
RUN;
data price_cooker_trend2 (drop=seller_type);
set price_cooker_trend1;
where seller_type = '3P New';
run;
data price_cooker_trend3;
set price_cooker_trend1;
where seller_type ne '3P New';
run;
proc freq data = price_cooker_trend3;
tables seller_type;
run;
PROC SORT DATA = seller_cooker_clusters_all (KEEP= SELLER_ID seller_type) OUT= SELLER_TYPE1;
BY SELLER_ID ;
RUN;

DATA newSELLER_TYPE_TREND ;
MERGE SELLER_TYPE1 (IN=A) price_cooker_trend2 (IN=B);
BY SELLER_ID ;
IF B;
RUN;
/*proc print data = newSELLER_TYPE_TREND (obs=20);*/
/*run; */
data SELLER_TYPE_TREND;
set newSELLER_TYPE_TREND price_cooker_trend3;
run;
proc freq data = SELLER_TYPE_TREND;
tables seller_type;
run;
PROC CONTENTS DATA = price_cooker_trend2;
RUN;
proc print data = SELLER_TYPE_TREND (obs=50);
run;

/*PROC EXPORT */
/*DATA=SELLER_TYPE_TREND*/
/*DBMS=csv*/
/*OUTFILE= "C:\Dynamic Pricing\Data\Data Cleaning and Summary Statistics\Home and Kitchen\Sellerclusters_cooker_110617.csv"*/
/*REPLACE;*/
/*run;*/
/*PROC EXPORT */
/*DATA=SELLER_TYPE_TREND*/
/*DBMS=csv*/
/*OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Home_Kitchen\Cluster Analysis\Sellerclusters_cooker_Feb2020.csv"*/
/*REPLACE;*/
/*run;*/



/***MERGING THIS BACK TO THE DATA SETS ON LOWEST PRICE OFFER***/
PROC SORT DATA = SELLER_TYPE_TREND;
BY TIME1 ASIN;
RUN;

DATA SELLER_TYPE_TREND1;
MERGE SELLER_TYPE_TREND (IN=A) lowestprice_offer (IN=B);
BY TIME1 ASIN;
IF A ;
RUN;
PROC EXPORT 
DATA=SELLER_TYPE_TREND1
DBMS=csv
OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Home_Kitchen\Cluster Analysis\Sellerclusters_cooker_July2025.csv"
REPLACE;
run;
PROC PRINT DATA = SELLER_TYPE_TREND1 (OBS=20);
RUN;

/**COMPUTING PRICE CHANGE BY ASIN AND SELLER TYPE***/

proc sort data = SELLER_TYPE_TREND1 NODUPREC out=SELLER_TYPE_TREND11 ;
by  ASIN seller_type seller_ID TIME1 ;
run;
proc expand data=SELLER_TYPE_TREND11 out=SELLER_TYPE_TREND12 method = none; 
  by ASIN seller_type seller_ID;
  convert price1 = price_lag1     / transformout=(lag 1); 
RUN;

proc sql;
create table price_changes_sellertype as
select 
time1,
ASIN,
focal_brand,
Nonfocal_brand1, 
Nonfocal_brand2,
Nonfocal_brand3, 
Nonfocal_brand4, 
Nonfocal_brand5, 
Nonfocal_brand6, 
Nonfocal_brand7, 
Nonfocal_brand8, 
Nonfocal_brand9, 
Nonfocal_brand10,
Nonfocal_brand11,
Nonfocal_brand12,
Nonfocal_brand13,
seller_type,
seller_ID,
price1,
(price1 - lowestprice_offer) as pricediff_fromlowest,
(case when price_lag1 ne . and price_lag1 ne 0 then (price1-price_lag1)/price_lag1 else 0 end) as pricechange

from
SELLER_TYPE_TREND12
group by
time1,
ASIN,
focal_brand,
Nonfocal_brand1, 
Nonfocal_brand2,
Nonfocal_brand3, 
Nonfocal_brand4, 
Nonfocal_brand5, 
Nonfocal_brand6, 
Nonfocal_brand7, 
Nonfocal_brand8, 
Nonfocal_brand9, 
Nonfocal_brand10,
Nonfocal_brand11,
Nonfocal_brand12,
Nonfocal_brand13,
seller_type,
seller_ID;
quit;
/*proc print data = price_changes_sellertype (obs=20);*/
/*var ASIN seller_ID price1 pricediff_fromlowest pricechange;*/
/*run;*/

/*PROC EXPORT */
/*DATA=price_changes_sellertype*/
/*DBMS=csv*/
/*OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Home_Kitchen\Cluster Analysis\price_changes_sellertype_Feb2020.csv"*/
/*REPLACE;*/
/*run;*/
PROC EXPORT 
DATA=price_changes_sellertype
DBMS=csv
OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Home_Kitchen\Cluster Analysis\price_changes_sellertype_July2025.csv"
REPLACE;
run;

/*PROC FREQ DATA = SELLER_TYPE_TREND12;*/
/*TABLES SELLER_TYPE;*/
/*RUN;*/
/***count number of price changes by seller type and ASIN***/

proc sql;
create table num_pricechange_sellertype_asin as
select
seller_ID, 
seller_type,
ASIN,
count(distinct time1) as num_days,
sum(case when pricechange ne 0 then 1 else 0 end) as num_pricechange
from
price_changes_sellertype
group by
seller_ID, 
seller_type,
ASIN;
quit;
/*PROC EXPORT */
/*DATA=num_pricechange_sellertype_asin*/
/*DBMS=csv*/
/*OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Home_Kitchen\Cluster Analysis\num_pricechange_sellertype_asin_Feb2020.csv"*/
/*REPLACE;*/
/*run;*/
PROC EXPORT 
DATA=num_pricechange_sellertype_asin
DBMS=csv
OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Home_Kitchen\Cluster Analysis\num_pricechange_sellertype_asin_July2025.csv"
REPLACE;
run;
/***selecting the representative seller and ASIN per cluster with most number of price changes in the period***/

proc sort data = num_pricechange_sellertype_asin out= sorted_bymostchanges ;
by seller_type descending num_pricechange descending num_days ASIN;
run;
PROC EXPORT 
DATA=sorted_bymostchanges
DBMS=csv
OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Home_Kitchen\Cluster Analysis\sorted_bymostchanges_Feb2020.csv"
REPLACE;
run;
data new3psellers;
set sorted_bymostchanges;
where seller_type in ('Cluster 1', 'Cluster 2', 'Cluster 3', 'Cluster 4', 'Cluster 5');
run;
/**by deduping the sorted file we retain the seller ID with most price changes in each seller type**/
proc sort data = new3psellers nodupkey out= new3p_pricechange_vector;
by seller_type;
run;
proc print data = new3p_pricechange_vector (obs=10);
run;
/**
Obs Seller_ID seller_type ASIN num_days num_pricechange 
1 A3F9MM2LBYKJ2U Cluster 1 B013I40R8E 142 10 
2 A19GJQAKBGLP50 Cluster 2 B0051O92U4 164 54 
3 A1RDYXN1JW1DJR Cluster 3 B003HF6PUO 275 39 
4 A3JAGHYFRKLQJ8 Cluster 4 B013I40R8E 348 124 
5 A2DU986DMKLTAE Cluster 5 B003HF6PUO 272 139 

%pricechange(a='Amazon New', b = Amzn_Breville, c = 'B013I40R8E'); 
%pricechange(a='Amazon New', b = Amzn_Cosori, c = 'B06VV9M11N'); 
%pricechange(a='Amazon New', b = Amzn_EP, c = 'B0051O92U4'); 
%pricechange(a='Amazon New', b = Amzn_IP, c = 'B01B1VC13K'); 
%pricechange(a='Amazon New', b = Amzn_Crockp, c = 'B003HF6PUO'); 
%pricechange(a='Cluster 1', b = clust1_Breville, c = 'B013I40R8E'); 
%pricechange(a='Cluster 2', b = clust2_EP, c = 'B0051O92U4'); 
%pricechange(a='Cluster 3', b = clust3_CrockP, c = 'B003HF6PUO'); 
%pricechange(a='Cluster 4', b = clust4_Breville, c = 'B013I40R8E'); 
%pricechange(a='Cluster 5', b = clust5_CrockP, c = 'B003HF6PUO' ); 
**/
/**proc means of average prices by other sellers of the selected analysis ASINS***/
/*data selectedasin_pricetrend;*/
/*set Seller_clusters_asin_price;*/
/*where ASIN in ('B013I40R8E', 'B0051O92U4', 'B003HF6PUO', 'B06VV9M11N', 'B01B1VC13K');*/
/*run;*/
/**/
/*proc means data = selectedasin_pricetrend n mean max min std;*/
/*class ASIN Seller_ID seller_type ;*/
/*var price1 ;*/
/*output out = avg_price ;*/
/*run;*/
/*proc means data = selectedasin_pricetrend;*/
/*class ASIN ;*/
/*var buy_box_price1;*/
/*output out = bb_avg_price;*/
/*run;*/

data amazon;
set sorted_bymostchanges;
where seller_type in ('Amazon New') and ASIN in ('B013I40R8E', 'B06VV9M11N', 'B0051O92U4', 'B01B1VC13K', 'B003HF6PUO');
run;
data newandamazonsellers;
set new3p_pricechange_vector amazon;
run;
proc print data = newandamazonsellers;
run;
/*proc freq data = newandamazonsellers;*/
/*tables seller_type*asin /list;*/
/*run;*/

/**merging back to the price changes main file to get the trend for the chosen seller IDs and ASINs**/
proc sort data = price_changes_sellertype out= price_changes_sellertype1;
by seller_type seller_ID ASIN;
run;

proc sort data = newandamazonsellers out= newandamazonsellers1;
by seller_type seller_ID ASIN;
run;


data pricechangetrend_outcome;
merge newandamazonsellers1 (in=a) price_changes_sellertype1 (in=b);
by seller_type seller_ID ASIN;
if a and b;
run;

proc sort data = pricechangetrend_outcome (keep=time1) nodupkey out= final_outputvector;
by time1;
run;
%macro pricechange(a,b,c);
data pricechange&b. (keep = time1 pricechange_&b. price&b. pricediff_lowst_&b. );
set pricechangetrend_outcome;
where seller_type = &a. and ASIN = &c.;
rename pricechange = pricechange_&b. ;
rename price1 = price&b.;
rename pricediff_fromlowest = pricediff_lowst_&b.;
run;

data final_outputvector ;
merge final_outputvector (in=a) pricechange&b. (in=b);
by time1;
if a ;
if a and not b then do;
pricechange_&b. = 0;
end;
run;

%mend;
%pricechange(a='Amazon New', b = Amzn_Breville, c = 'B013I40R8E'); 
%pricechange(a='Amazon New', b = Amzn_Cosori, c = 'B06VV9M11N'); 
%pricechange(a='Amazon New', b = Amzn_EP, c = 'B0051O92U4'); 
%pricechange(a='Amazon New', b = Amzn_IP, c = 'B01B1VC13K'); 
%pricechange(a='Amazon New', b = Amzn_CrockP, c = 'B003HF6PUO'); 
%pricechange(a='Cluster 1', b = clust1_Breville, c = 'B013I40R8E'); 
%pricechange(a='Cluster 2', b = clust2_EP, c = 'B0051O92U4'); 
%pricechange(a='Cluster 3', b = clust3_CrockP, c = 'B003HF6PUO'); 
%pricechange(a='Cluster 4', b = clust4_Breville, c = 'B013I40R8E'); 
%pricechange(a='Cluster 5', b = clust5_CrockP, c = 'B003HF6PUO' ); 
proc univariate data = final_outputvector;
run;
/*PROC EXPORT */
/*DATA=final_outputvector*/
/*DBMS=csv*/
/*OUTFILE= "C:\Users\F00456N\Documents\Amazon Price Dynamics\New Data\Data Scraped\Home_Kitchen\Cluster Analysis\pricechangetrend_outcome_Feb2020.csv"*/
/*REPLACE;*/
/*run;*/
PROC EXPORT 
DATA=final_outputvector
DBMS=csv
OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Home_Kitchen\Cluster Analysis\pricechangetrend_outcome_Jul2025.csv"
REPLACE;
run;
proc print data = final_outputvector (obs=200);
run;

/*****CODING COVARIATES*****/

PROC CONTENTS DATA = price_trend;
RUN;

/***CLUSTER MEMBERSHIP: NUMBER OF SELLERS IN A CLUSTER AT TIME T, BRANDS SOLD BY SELLER TYPES AT TIME T,
NUMBER OF PRICE CHANGES BY OWN AND CROSS BRANDS BY SELLER TYPES AT TIME (T-1), (T-2), (T-3), *******/


/*******NEW 3P SELLER CHARACTERISTICS: COUNTING NUMBER OF BRANDS OFFERED BY CLUSTER, COUNT OF SELLERS PER CLUSTER***/
/**brand 1: Geek Chef**/
/*brand 2: Cosori*/
/**Oyama**/
/**Elechomes**/
/**Avalon Bay**/
/**Hamilton Beach**/
/**Cusimax**/
/**Pressure Pro**/
/**Crock-Pot**/
 /**Magic Mill**/
 /**Cuisinart**/
 /**Breville**/
 /**Elite Platinum**/

/***COMPETITOR OFFER INFORMATION*****/

data SELLER_TYPE_TREND3;
set SELLER_TYPE_TREND12;
if seller_type in ('3P Old Focal', '3P Old Non-Focal') then seller_type = '3P Old';
else if seller_type in ('Amazon Old Focal', 'Amazon Old Non-Focal') then seller_type = 'Amazon Old';
else seller_type = seller_type;
run;

PROC SQL;
CREATE TABLE SELLER_offercharcs AS
SELECT
TIME1,
SELLER_TYPE,
COUNT(DISTINCT SELLER_ID) AS NUM_SELLERS,
COUNT(DISTINCT ASIN) AS NUM_SKUS,
SUM(focal_brand) AS NUM_IP,
sum(Nonfocal_brand1) as num_Gkchef, 
sum(Nonfocal_brand2) as num_Cosori,
sum(Nonfocal_brand3) as num_Oyama, 
sum(Nonfocal_brand4) as num_Elechms, 
sum(Nonfocal_brand5) as num_AvBay, 
sum(Nonfocal_brand6) as num_HBeach, 
sum(Nonfocal_brand7) as num_Cusimax, 
sum(Nonfocal_brand8) as num_PPro, 
sum(Nonfocal_brand9) as num_CrockP, 
sum(Nonfocal_brand10) as num_MMill,
sum(Nonfocal_brand11) as num_Cuisinart,
sum(Nonfocal_brand12) as num_Breville,
sum(Nonfocal_brand13) as num_EP
from 
SELLER_TYPE_TREND3
group by
time1,
seller_type;
quit;

/**/
/*proc freq data = SELLER_offercharcs;*/
/*tables seller_type;*/
/*run;*/
proc sort data = SELLER_offercharcs (keep = time1) nodupkey out= SELLER_offercharcs_base;
by time1;
run;

%macro seller_offercharacs(a,b);
data seller_offer&a.;
set SELLER_offercharcs;
where seller_type = &b.;
rename NUM_SELLERS = NUM_SELLERS&a.;
rename NUM_SKUS = NUM_SKUS&a.;
rename NUM_IP = NUM_IP&a.;
rename num_Gkchef = num_Gkchef&a.;
rename num_Cosori = num_Cosori&a.;
rename num_Oyama = num_Oyama&a.;
rename num_Elechms = num_Elechms&a.;
rename num_AvBay = num_AvBay&a.;
rename num_HBeach = num_HBeach&a.;
rename num_Cusimax = num_Cusimax&a.;
rename num_PPro = num_PPro&a.;
rename num_CrockP = num_CrockP&a.;
rename num_MMill = num_MMill&a.;
rename num_Cuisinart = num_Cuisinart&a.;
rename num_Breville = num_Breville&a.;
rename num_EP = num_EP&a.;
run;

proc sort data = seller_offer&a. (drop=  seller_type) out= sorted_seller_type;
by time1;
run;

data SELLER_offercharcs_base;
merge SELLER_offercharcs_base (in=a) sorted_seller_type (in=b);
by time1;
if a ;
if a and not b then do;
NUM_SELLERS&a. = 0;
NUM_SKUS&a. = 0;
NUM_IP&a. = 0;
num_Gkchef&a.= 0;
num_Cosori&a. = 0;
num_Oyama&a. = 0;
num_Elechms&a. = 0;
num_AvBay&a. = 0;
num_HBeach&a. = 0;
num_Cusimax&a. = 0;
num_PPro&a. = 0;
num_CrockP&a. = 0;
num_MMill&a. = 0;
num_Cuisinart&a. = 0;
num_Breville&a. = 0;
num_EP&a. = 0;
end;
run;
%mend;
%seller_offercharacs(a = used3p, b = '3P Old');
%seller_offercharacs(a = usedamzn, b = 'Amazon Old'); 
%seller_offercharacs(a = amzn, b = 'Amazon New');
%seller_offercharacs(a = clust1, b = 'Cluster 1'); 
%seller_offercharacs(a = clust2, b = 'Cluster 2'); 
%seller_offercharacs(a = clust3, b = 'Cluster 3');
%seller_offercharacs(a = clust4, b = 'Cluster 4');
%seller_offercharacs(a = clust5, b = 'Cluster 5'); 
/*****COMPETITOR PRICING INFORMATION*****/
/**ACCOUNTING FOR PRICE CHANGES IN EACH SELLER TYPE BY BRANDS****/

proc sql;
create table num_pricechange_sellertype_asin as
select
TIME1,
seller_type,
ASIN,
sum(case when pricechange ne 0 then 1 else 0 end) as num_pricechange
from
price_changes_sellertype
group by
time1,
seller_type,
ASIN;
quit;
proc freq data = price_changes_sellertype;
tables seller_type;
run;
proc sort data = num_pricechange_sellertype_asin (keep = time1)nodupkey out= numpricechange_sellerbrandtrend;
by time1;
run;
%macro seller_brand(a,b,c,d);
data num_pricechange_&d.&b.;
set num_pricechange_sellertype_asin;
where ASIN in &c. and seller_type = &a.;
rename num_pricechange = num_pricechange&b.&d.;
run;

proc sort data = num_pricechange_&d.&b. (drop= ASIN seller_type) NODUPKEY out= sorted;
by time1;
run;

data numpricechange_sellerbrandtrend;
merge numpricechange_sellerbrandtrend (in=a) sorted (in=b);
by time1;
if a ;
if a and not b then num_pricechange&b.&d. = 0;
run;
%mend;

%seller_brand(a='3P Old Focal',b= IP, c=('B00FLYWNYQ','B00N310CKG','B01B1VC13K','B01DBB96JU','B01MFEBQH1', 'B01NBKTPTS','B06Y1MP2PY'), d= used3p);
%seller_brand(a='Amazon Old Focal',b= IP, c=('B00FLYWNYQ','B00N310CKG','B01B1VC13K','B01DBB96JU','B01MFEBQH1', 'B01NBKTPTS','B06Y1MP2PY'), d= useamz);
%seller_brand(a='Amazon New',b= IP, c=('B00FLYWNYQ','B00N310CKG','B01B1VC13K','B01DBB96JU','B01MFEBQH1', 'B01NBKTPTS','B06Y1MP2PY'), d= amzn);
%seller_brand(a='Cluster 1',b= IP, c=('B00FLYWNYQ','B00N310CKG','B01B1VC13K','B01DBB96JU','B01MFEBQH1', 'B01NBKTPTS','B06Y1MP2PY'), d= clust1);
%seller_brand(a='Cluster 2',b= IP, c=('B00FLYWNYQ','B00N310CKG','B01B1VC13K','B01DBB96JU','B01MFEBQH1', 'B01NBKTPTS','B06Y1MP2PY'), d= clust2);
%seller_brand(a='Cluster 3',b= IP, c=('B00FLYWNYQ','B00N310CKG','B01B1VC13K','B01DBB96JU','B01MFEBQH1', 'B01NBKTPTS','B06Y1MP2PY'), d= clust3);
%seller_brand(a='Cluster 4',b= IP, c=('B00FLYWNYQ','B00N310CKG','B01B1VC13K','B01DBB96JU','B01MFEBQH1', 'B01NBKTPTS','B06Y1MP2PY'), d= clust4);
%seller_brand(a='Cluster 5',b= IP, c=('B00FLYWNYQ','B00N310CKG','B01B1VC13K','B01DBB96JU','B01MFEBQH1', 'B01NBKTPTS','B06Y1MP2PY'), d= clust5);

%seller_brand(a='3P Old Non-Focal',b= gkchf, c=('B01M0X67O0'), d= used3p);
%seller_brand(a='Amazon Old Non-Focal',b= gkchf, c=('B01M0X67O0'), d= useamz);
%seller_brand(a='Amazon New',b= gkchf, c=('B01M0X67O0'), d= amzn);
%seller_brand(a='Cluster 1',b= gkchf, c=('B01M0X67O0'), d= clust1);
%seller_brand(a='Cluster 2',b= gkchf, c=('B01M0X67O0'), d= clust2);
%seller_brand(a='Cluster 3',b= gkchf, c=('B01M0X67O0'), d= clust3);
%seller_brand(a='Cluster 4',b= gkchf, c=('B01M0X67O0'), d= clust4);
%seller_brand(a='Cluster 5',b= gkchf, c=('B01M0X67O0'), d= clust5);

%seller_brand(a='3P Old Non-Focal',b= cosori, c=('B06VV9M11N'), d= used3p);
%seller_brand(a='Amazon Old Non-Focal',b= cosori, c=('B06VV9M11N'), d= useamz);
%seller_brand(a='Amazon New',b= cosori, c=('B06VV9M11N'), d= amzn);
%seller_brand(a='Cluster 1',b= cosori, c=('B06VV9M11N'), d= clust1);
%seller_brand(a='Cluster 2',b= cosori, c=('B06VV9M11N'), d= clust2);
%seller_brand(a='Cluster 3',b= cosori, c=('B06VV9M11N'), d= clust3);
%seller_brand(a='Cluster 4',b= cosori, c=('B06VV9M11N'), d= clust4);
%seller_brand(a='Cluster 5',b= cosori, c=('B06VV9M11N'), d= clust5);

%seller_brand(a='3P Old Non-Focal',b= oyama, c=('B01MYAJQES'), d= used3p);
%seller_brand(a='Amazon Old Non-Focal',b= oyama, c=('B01MYAJQES'), d= useamz);
%seller_brand(a='Amazon New',b= oyama, c=('B01MYAJQES'), d= amzn);
%seller_brand(a='Cluster 1',b= oyama, c=('B01MYAJQES'), d= clust1);
%seller_brand(a='Cluster 2',b= oyama, c=('B01MYAJQES'), d= clust2);
%seller_brand(a='Cluster 3',b= oyama, c=('B01MYAJQES'), d= clust3);
%seller_brand(a='Cluster 4',b= oyama, c=('B01MYAJQES'), d= clust4);
%seller_brand(a='Cluster 5',b= oyama, c=('B01MYAJQES'), d= clust5);

%seller_brand(a='3P Old Non-Focal',b= elechms, c=('B01NBC616L'), d= used3p);
%seller_brand(a='Amazon Old Non-Focal',b= elechms, c=('B01NBC616L'), d= useamz);
%seller_brand(a='Amazon New',b= elechms, c=('B01NBC616L'), d= amzn);
%seller_brand(a='Cluster 1',b= elechms, c=('B01NBC616L'), d= clust1);
%seller_brand(a='Cluster 2',b= elechms, c=('B01NBC616L'), d= clust2);
%seller_brand(a='Cluster 3',b= elechms, c=('B01NBC616L'), d= clust3);
%seller_brand(a='Cluster 4',b= elechms, c=('B01NBC616L'), d= clust4);
%seller_brand(a='Cluster 5',b= elechms, c=('B01NBC616L'), d= clust5);

%seller_brand(a='3P Old Non-Focal',b= avbay, c=('B06XP79M4F'), d= used3p);
%seller_brand(a='Amazon Old Non-Focal',b= avbay, c=('B06XP79M4F'), d= useamz);
%seller_brand(a='Amazon New',b= avbay, c=('B06XP79M4F'), d= amzn);
%seller_brand(a='Cluster 1',b= avbay, c=('B06XP79M4F'), d= clust1);
%seller_brand(a='Cluster 2',b= avbay, c=('B06XP79M4F'), d= clust2);
%seller_brand(a='Cluster 3',b= avbay, c=('B06XP79M4F'), d= clust3);
%seller_brand(a='Cluster 4',b= avbay, c=('B06XP79M4F'), d= clust4);
%seller_brand(a='Cluster 5',b= avbay, c=('B06XP79M4F'), d= clust5);

%seller_brand(a='3P Old Non-Focal',b= hbeach, c=('B00EZI26C8'), d= used3p);
%seller_brand(a='Amazon Old Non-Focal',b= hbeach, c=('B00EZI26C8'), d= useamz);
%seller_brand(a='Amazon New',b= hbeach, c=('B00EZI26C8'), d= amzn);
%seller_brand(a='Cluster 1',b= hbeach, c=('B00EZI26C8'), d= clust1);
%seller_brand(a='Cluster 2',b= hbeach, c=('B00EZI26C8'), d= clust2);
%seller_brand(a='Cluster 3',b= hbeach, c=('B00EZI26C8'), d= clust3);
%seller_brand(a='Cluster 4',b= hbeach, c=('B00EZI26C8'), d= clust4);
%seller_brand(a='Cluster 5',b= hbeach, c=('B00EZI26C8'), d= clust5);

%seller_brand(a='3P Old Non-Focal',b= cusimax, c=('B06XCMYN1H'), d= used3p);
%seller_brand(a='Amazon Old Non-Focal',b= cusimax, c=('B06XCMYN1H'), d= useamz);
%seller_brand(a='Amazon New',b= cusimax, c=('B06XCMYN1H'), d= amzn);
%seller_brand(a='Cluster 1',b= cusimax, c=('B06XCMYN1H'), d= clust1);
%seller_brand(a='Cluster 2',b= cusimax, c=('B06XCMYN1H'), d= clust2);
%seller_brand(a='Cluster 3',b= cusimax, c=('B06XCMYN1H'), d= clust3);
%seller_brand(a='Cluster 4',b= cusimax, c=('B06XCMYN1H'), d= clust4);
%seller_brand(a='Cluster 5',b= cusimax, c=('B06XCMYN1H'), d= clust5);

%seller_brand(a='3P Old Non-Focal',b= ppro, c=('B00YT6SY72'), d= used3p);
%seller_brand(a='Amazon Old Non-Focal',b= ppro, c=('B00YT6SY72'), d= useamz);
%seller_brand(a='Amazon New',b= ppro, c=('B00YT6SY72'), d= amzn);
%seller_brand(a='Cluster 1',b= ppro, c=('B00YT6SY72'), d= clust1);
%seller_brand(a='Cluster 2',b= ppro, c=('B00YT6SY72'), d= clust2);
%seller_brand(a='Cluster 3',b= ppro, c=('B00YT6SY72'), d= clust3);
%seller_brand(a='Cluster 4',b= ppro, c=('B00YT6SY72'), d= clust4);
%seller_brand(a='Cluster 5',b= ppro, c=('B00YT6SY72'), d= clust5);

%seller_brand(a='3P Old Non-Focal',b= crockp, c=('B003HF6PUO'), d= used3p);
%seller_brand(a='Amazon Old Non-Focal',b= crockp, c=('B003HF6PUO'), d= useamz);
%seller_brand(a='Amazon New',b= crockp, c=('B003HF6PUO'), d= amzn);
%seller_brand(a='Cluster 1',b= crockp, c=('B003HF6PUO'), d= clust1);
%seller_brand(a='Cluster 2',b= crockp, c=('B003HF6PUO'), d= clust2);
%seller_brand(a='Cluster 3',b= crockp, c=('B003HF6PUO'), d= clust3);
%seller_brand(a='Cluster 4',b= crockp, c=('B003HF6PUO'), d= clust4);
%seller_brand(a='Cluster 5',b= crockp, c=('B003HF6PUO'), d= clust5);

%seller_brand(a='3P Old Non-Focal',b= mmill, c=('B01FIN77J0'), d= used3p);
%seller_brand(a='Amazon Old Non-Focal',b= mmill, c=('B01FIN77J0'), d= useamz);
%seller_brand(a='Amazon New',b= mmill, c=('B01FIN77J0'), d= amzn);
%seller_brand(a='Cluster 1',b= mmill, c=('B01FIN77J0'), d= clust1);
%seller_brand(a='Cluster 2',b= mmill, c=('B01FIN77J0'), d= clust2);
%seller_brand(a='Cluster 3',b= mmill, c=('B01FIN77J0'), d= clust3);
%seller_brand(a='Cluster 4',b= mmill, c=('B01FIN77J0'), d= clust4);
%seller_brand(a='Cluster 5',b= mmill, c=('B01FIN77J0'), d= clust5);

%seller_brand(a='3P Old Non-Focal',b= cuisinart, c=('B000MPA044'), d= used3p);
%seller_brand(a='Amazon Old Non-Focal',b= cuisinart, c=('B000MPA044'), d= useamz);
%seller_brand(a='Amazon New',b= cuisinart, c=('B000MPA044'), d= amzn);
%seller_brand(a='Cluster 1',b= cuisinart, c=('B000MPA044'), d= clust1);
%seller_brand(a='Cluster 2',b= cuisinart, c=('B000MPA044'), d= clust2);
%seller_brand(a='Cluster 3',b= cuisinart, c=('B000MPA044'), d= clust3);
%seller_brand(a='Cluster 4',b= cuisinart, c=('B000MPA044'), d= clust4);
%seller_brand(a='Cluster 5',b= cuisinart, c=('B000MPA044'), d= clust5);

%seller_brand(a='3P Old Non-Focal',b= breville, c=('B013I40R8E'), d= used3p);
%seller_brand(a='Amazon Old Non-Focal',b= breville, c=('B013I40R8E'), d= useamz);
%seller_brand(a='Amazon New',b= breville, c=('B013I40R8E'), d= amzn);
%seller_brand(a='Cluster 1',b= breville, c=('B013I40R8E'), d= clust1);
%seller_brand(a='Cluster 2',b= breville, c=('B013I40R8E'), d= clust2);
%seller_brand(a='Cluster 3',b= breville, c=('B013I40R8E'), d= clust3);
%seller_brand(a='Cluster 4',b= breville, c=('B013I40R8E'), d= clust4);
%seller_brand(a='Cluster 5',b= breville, c=('B013I40R8E'), d= clust5);

%seller_brand(a='3P Old Non-Focal',b= EP, c=('B0051O92U4'), d= used3p);
%seller_brand(a='Amazon Old Non-Focal',b= EP, c=('B0051O92U4'), d= useamz);
%seller_brand(a='Amazon New',b= EP, c=('B0051O92U4'), d= amzn);
%seller_brand(a='Cluster 1',b= EP, c=('B0051O92U4'), d= clust1);
%seller_brand(a='Cluster 2',b= EP, c=('B0051O92U4'), d= clust2);
%seller_brand(a='Cluster 3',b= EP, c=('B0051O92U4'), d= clust3);
%seller_brand(a='Cluster 4',b= EP, c=('B0051O92U4'), d= clust4);
%seller_brand(a='Cluster 5',b= EP, c=('B0051O92U4'), d= clust5);

/****************************/
/*****SELLER ATTRIBUTES******/
/****************************/
/***EXTRACTING NUM OF UNIQUE ASINS AND NUM OF ASINS SOLD CONDITION FOR EACH SELLER***/
PROC SQL;
create table seller_asin as
select
time1,
seller_ID,
count(distinct ASIN) as unique_ASIN_sold,
count(distinct time1) as sell_freq
from
SELLER_TYPE_TREND3
group by
time1,
seller_ID
;
quit;


/**MERGING BACK TO THE MAIN DATA SET**/
PROC SORT DATA = SELLER_TYPE_TREND3 OUT= SELLER_TYPE_TREND4;
BY time1 seller_ID;
RUN;
PROC SORT DATA = seller_asin OUT= seller_asin1;
BY time1 seller_ID;
RUN;
DATA SELLER_TYPE_TREND5;
MERGE SELLER_TYPE_TREND4 (IN=A) seller_asin1 (IN=B);
BY time1 seller_ID;
IF A AND B ;
RUN;

/**creating cumulative sell freq over time***/
proc sort data = SELLER_TYPE_TREND5 (keep= time1 seller_ID sell_freq) nodupkey out=SELLER_TYPE_TREND6;
by seller_ID time1;
run;

data cum_sellfreq;
  set SELLER_TYPE_TREND6;
  by seller_ID ;
  if first.seller_ID then cum_sellerfreq = 0;
  cum_sellerfreq +sell_freq;
run;

/**merging back to main data set**/
PROC SORT DATA = SELLER_TYPE_TREND5 OUT= SELLER_TYPE_TREND7;
BY time1 seller_ID;
RUN;
PROC SORT DATA = cum_sellfreq OUT= cum_sellfreq1;
BY time1 seller_ID;
RUN;
DATA SELLER_TYPE_TREND8;
MERGE SELLER_TYPE_TREND7 (IN=A) cum_sellfreq1 (IN=B);
BY time1 seller_ID;
IF A AND B ;
RUN;
PROC FREQ DATA = SELLER_TYPE_TREND8;
TABLES FREESHIP;
RUN;
PROC SQL;
create table SELLER_CHAR as
select
time1,
seller_type,
SUM(FBA) AS NUM_FBA,
sum(prime) as num_prime,
SUM(FREESHIP) AS NUM_FREESHIP,
SUM(CONDITION) AS NUM_NEWOFFERS,
(COUNT(CONDITION) - SUM(CONDITION)) AS NUM_USEDOFFERS, /**this needs to use case when*/
max(num_seller_Posrating) as max_perct_sellerrating,
mean(num_seller_Posrating) as mean_perct_sellerrating,
min(num_seller_Posrating) as min_perct_sellerrating,
max(num_seller_ratings) as max_num_seller_rating,
mean(num_seller_ratings) as mean_num_seller_rating,
min(num_seller_ratings) as min_num_seller_rating,
max(seller_rate) as max_seller_star_rating,
mean(seller_rate) as mean_seller_star_rating,
min(seller_rate) as min_seller_star_rating,
max(unique_ASIN_sold) as max_uniq_ASIN,
mean(unique_ASIN_sold) as mean_uniq_ASIN,
min(unique_ASIN_sold) as min_uniq_ASIN,
max(cum_sellerfreq) as max_cumsalefreq,
mean(cum_sellerfreq) as mean_cumsalefreq,
min(cum_sellerfreq) as min_cumsalefreq
from
SELLER_TYPE_TREND8
group by
time1,
seller_type
;
quit;
proc print data = SELLER_TYPE_TREND8 (obs = 20);
var cum_sellerfreq;
where seller_type = 'Amazon Old';
run;

/**rename each seller char to individual columns for each seller cluster for model build, i.e., max_fba_clust1, min_fba_clust1 etc. ****/

proc sort data = SELLER_CHAR  (keep=time1) nodupkey out=seller_char_sorted;
by time1;
run;
data sellerchar_amzn;
set SELLER_CHAR;
where seller_type = 'Amazon New';
rename num_Freeship = Freeship_amzn;
rename num_prime = prime_amzn;
rename max_cumsalefreq = cumsalefreq_amzn;
run;
data sellerchar_usedamzn;
set SELLER_CHAR;
where seller_type = 'Amazon Old';
rename num_Freeship = Freeship_usedamzn;
run;
data sellerchar_used3p;
set SELLER_CHAR;
where seller_type = '3P Old';
rename num_FBA = FBA_used3p;
rename num_prime = prime_used3p;
rename num_Freeship = Freeship_used3p;
run;
proc sort data = sellerchar_amzn (keep = time1 Freeship_amzn cumsalefreq_amzn prime_amzn) out= sellerchar_amzn1;
by time1;
run;

proc sort data = sellerchar_usedamzn (keep = time1 Freeship_usedamzn  ) out= sellerchar_amznused1;
by time1;
run;
proc sort data = sellerchar_used3p (keep = time1 FBA_used3p Freeship_used3p prime_used3p ) out= sellerchar_used3p1;
by time1;
run;
data seller_char_sorted ;
merge seller_char_sorted (in=a) sellerchar_amzn1 (in=b) sellerchar_amznused1 (in =c) sellerchar_used3p1 (in=d);
by time1;
if a ;
if  a and not b then do;
Freeship_amzn = 0;
cumsalefreq_amzn = 0;
prime_amzn = 0;
end;
if a and not c then do;
Freeship_usedamzn = 0;
end;
if a and not d then do;
FBA_used3p = 0;
Freeship_used3p = 0;
prime_used3p = 0;

end;
run;


%macro sellerchar(a,b);
data sellerchar_&b.;
set SELLER_CHAR;
where seller_type = &a.;
rename num_FBA = num_FBA_&b.;
rename num_prime = num_prime_&b.;
rename num_Freeship = num_Freeship_&b.;
rename max_perct_sellerrating = max_perct_sellerrating_&b.;
rename mean_perct_sellerrating = mean_perct_sellerrating_&b.;
rename min_perct_sellerrating = min_perct_sellerrating_&b.;
rename max_num_seller_rating = max_num_seller_rating_&b.;
rename mean_num_seller_rating = mean_num_seller_rating_&b.;
rename min_num_seller_rating = min_num_seller_rating_&b.;
rename max_seller_star_rating = max_seller_star_rating_&b.;
rename mean_seller_star_rating = mean_seller_star_rating_&b.;
rename min_seller_star_rating = min_seller_star_rating_&b.;
rename max_uniq_ASIN = max_uniq_ASIN_&b.;
rename mean_uniq_ASIN = mean_uniq_ASIN_&b.;
rename min_uniq_ASIN = min_uniq_ASIN_&b.;
rename max_cumsalefreq = max_cumsalefreq_&b.;
rename mean_cumsalefreq = mean_cumsalefreq_&b.;
rename min_cumsalefreq = min_cumsalefreq_&b.;
run;

proc sort data = sellerchar_&b. (keep = time1 num_FBA_&b. num_prime_&b. num_Freeship_&b. max_perct_sellerrating_&b. 
mean_perct_sellerrating_&b. min_perct_sellerrating_&b. max_num_seller_rating_&b. mean_num_seller_rating_&b.
min_num_seller_rating_&b. max_seller_star_rating_&b. mean_seller_star_rating_&b. min_seller_star_rating_&b. 
max_uniq_ASIN_&b. mean_uniq_ASIN_&b. min_uniq_ASIN_&b. max_cumsalefreq_&b. mean_cumsalefreq_&b. 
min_cumsalefreq_&b.) out= sellerchar_sorted&b.;
by time1;
run;

data seller_char_sorted ;
merge seller_char_sorted (in=a) sellerchar_sorted&b. (in=b);
by time1;
if a ;
if a and not b then do;
num_FBA_&b. = 0;
num_prime_&b.=0;
num_Freeship_&b. = 0;
max_perct_sellerrating_&b. = 0;
mean_perct_sellerrating_&b. = 0;
min_perct_sellerrating_&b. = 0;
max_num_seller_rating_&b. = 0;
mean_num_seller_rating_&b. = 0;
min_num_seller_rating_&b. = 0;
max_seller_star_rating_&b. = 0;
mean_seller_star_rating_&b. = 0;
min_seller_star_rating_&b. = 0;
max_uniq_ASIN_&b. = 0;
mean_uniq_ASIN_&b. = 0;
min_uniq_ASIN_&b. = 0;
max_cumsalefreq_&b. = 0;
mean_cumsalefreq_&b. = 0;
min_cumsalefreq_&b. = 0;
end;
run;
%mend;
%sellerchar(a='Cluster 1', b = clust1); 
%sellerchar(a='Cluster 2', b = clust2); 
%sellerchar(a='Cluster 3', b = clust3); 
%sellerchar(a='Cluster 4', b = clust4); 
%sellerchar(a='Cluster 5', b = clust5);

/***********************************/
/*************BRAND ATTRIBUTES*****/
/*********************************/

proc contents data = SELLER_TYPE_TREND3;
run;

/**SUMMARIZING PRODUCT CHAR FOR INSTANT POT***/
PROC SQL;
create table PRODUCT_CHAR_IP as
select
time1,
MAX(Answered_Questions) as max_ans_questions_IP,
mean(Answered_Questions) as mean_ans_questions_IP,
min(Answered_Questions) as min_ans_questions_IP,
max(Buy_Box_Price1) as max_buyboxprice_IP,
mean(Buy_Box_Price1) as mean_buyboxprice_IP,
min(Buy_Box_Price1) as min_buyboxprice_IP,
max(nonbuybox3p_price) as max_nonbuybox3p_price_IP,
mean(nonbuybox3p_price) as mean_nonbuybox3p_price_IP,
min(nonbuybox3p_price) as min_nonbuybox3p_price_IP,
max(Product_Star_Rate) as max_product_star_rate_IP,
mean(Product_Star_Rate) as mean_product_star_rate_IP,
min(Product_Star_Rate) as min_product_star_rate_IP,
max(Product_reviews) as max_Product_reviews_IP,
mean(Product_reviews) as mean_Product_reviews_IP,
min(Product_reviews) as min_Product_reviews_IP,
max(Sales_Rank_Own_Categ) as max_salesrank_categ_IP,
mean(Sales_Rank_Own_Categ) as mean_salesrank_categ_IP,
min(Sales_Rank_Own_Categ) as min_salesrank_categ_IP,
max(Sales_Rank_Sub_categ) as max_salesrank_subcateg_IP,
mean(Sales_Rank_Sub_categ) as mean_salesrank_subcateg_IP,
min(Sales_Rank_Sub_categ) as min_salesrank_subcateg_IP
from
focal_rival_prods27
WHERE ASIN IN ('B00FLYWNYQ','B00N310CKG','B01B1VC13K','B01DBB96JU','B01MFEBQH1', 'B01NBKTPTS','B06Y1MP2PY')
group by
time1
;
quit;
proc print data = PRODUCT_CHAR_IP (obs=20);
run;


/**MERGING BACK TO THE SELLER CHARACTERISTICS DATA***/
DATA seller_char_sorted;
MERGE seller_char_sorted (IN=A) PRODUCT_CHAR_IP (IN=B) SELLER_offercharcs_base (in=c);
BY TIME1;
IF A ;
IF A AND NOT B THEN DO;
max_ans_questions_IP =0;
mean_ans_questions_IP =0;
min_ans_questions_IP =0;
max_buyboxprice_IP =0;
mean_buyboxprice_IP =0;
min_buyboxprice_IP =0;
max_nonbuybox3p_price_IP = 0;
mean_nonbuybox3p_price_IP = 0;
min_nonbuybox3p_price_IP = 0;
max_product_star_rate_IP =0;
mean_product_star_rate_IP =0;
min_product_star_rate_IP =0;
max_Product_reviews_IP =0;
mean_Product_reviews_IP =0;
min_Product_reviews_IP =0;
max_salesrank_categ_IP =0;
mean_salesrank_categ_IP =0;
min_salesrank_categ_IP =0;
max_salesrank_subcateg_IP =0;
mean_salesrank_subcateg_IP =0;
min_salesrank_subcateg_IP =0;
END;
RUN;
proc print data = focal_rival_prods26 (obs=20);
run;

proc sort data = seller_char_sorted out= ALL_char_sorted ;
by time1;
run;
%macro prodchar(a,b);
data prodchar_&b.;
set focal_rival_prods27;
where ASIN = &a.;
rename Answered_Questions = Answered_Questions&b. ;
rename Buy_Box_Price1 = buyboxprice_&b.;
rename nonbuybox3p_price = nonbuybox3p_price_&b.;
rename Product_Star_Rate = product_star_&b.;
rename Product_reviews = Product_reviews_&b.;
rename Sales_Rank_Own_Categ = Sales_Rank_Own_Categ_&b.;
rename Sales_Rank_Sub_categ = Sales_Rank_Sub_categ_&b.;
run;

proc sort data = prodchar_&b. (keep = time1  Answered_Questions&b. buyboxprice_&b. nonbuybox3p_price_&b. product_star_&b. 
Product_reviews_&b. Sales_Rank_Own_Categ_&b. Sales_Rank_Sub_categ_&b.) NODUPKEY out= prodchar_sorted&b.;
by time1;
run;

data ALL_char_sorted ;
merge ALL_char_sorted (in=a) prodchar_sorted&b. (in=b);
by time1;
if a;
IF A AND NOT B THEN DO;
Answered_Questions&b. = 0;
buyboxprice_&b. = 0;
nonbuybox3p_price_&b. = 0;
product_star_&b. = 0;
Product_reviews_&b. = 0;
Sales_Rank_Own_Categ_&b. = 0;
Sales_Rank_Sub_categ_&b. = 0;
END;
run;
%mend;
%prodchar(a= 'B01M0X67O0', b = gkchf); 
%prodchar(a= 'B06VV9M11N', b = cosori);
%prodchar(a= 'B01MYAJQES', b = oyama);
%prodchar(a= 'B01NBC616L', b = elechms);
%prodchar(a= 'B06XP79M4F', b = avbay);
%prodchar(a= 'B00EZI26C8', b = hbeach);
%prodchar(a= 'B06XCMYN1H', b= cusimax);
%prodchar(a= 'B00YT6SY72', b= ppro);
%prodchar(a= 'B003HF6PUO', b= crockp);
%prodchar(a= 'B01FIN77J0', b= mmill);
%prodchar(a= 'B000MPA044', b= cuisinart);
%prodchar(a= 'B013I40R8E', b= breville);
%prodchar(a= 'B0051O92U4', b= EP);
proc print data = ALL_char_sorted (obs=200);
var nonbuybox3p_price_breville nonbuybox3p_price_cosori;
run;
proc contents data = ALL_char_sorted;
run;

/**MERGING WITH BRAND SELLER PRICE CHANGE TREND**/
DATA ALL_COVARIATES ;
MERGE ALL_char_sorted (IN = A) numpricechange_sellerbrandtrend (IN=B) ;
BY TIME1;
IF A ;
RUN;
data inn.all_covariates;
set all_covariates;
run;
proc contents data = final_outputvector;
run;
/*****merging with final outcome vector****/

proc sort data = final_outputvector ;
by time1;
run;

data merge_finaldata;
merge final_outputvector (in=a) ALL_COVARIATES (in=b);
by time1;
if a;
rename pricechange_Amzn_Breville = Y_amzn_breville;
rename pricechange_Amzn_Cosori = Y_amzn_cosori;
rename pricechange_Amzn_EP = Y_amzn_ep;
rename pricechange_Amzn_IP = Y_amzn_ip;
rename pricechange_Amzn_CrockP = Y_amzn_crockp;
rename pricechange_clust1_Breville = Y_clust1_breville;
rename pricechange_clust2_EP = Y_clust2_ep;
rename pricechange_clust3_CrockP = Y_clust3_crockp;
rename pricechange_clust4_Breville = Y_clust4_breville;
rename pricechange_clust5_CrockP = Y_clust5_crockp;

run;


proc print data = merge_finaldata (obs=200);
var priceAmzn_Breville
priceAmzn_Cosori
priceAmzn_EP
priceAmzn_IP
priceAmzn_CrockP
priceclust1_Breville
priceclust2_EP
priceclust3_CrockP
priceclust4_Breville
priceclust5_CrockP
pricediff_lowst_Amzn_Breville
pricediff_lowst_Amzn_Cosori
pricediff_lowst_Amzn_EP
pricediff_lowst_Amzn_IP
pricediff_lowst_Amzn_CrockP
pricediff_lowst_clust1_Breville
pricediff_lowst_clust2_EP
pricediff_lowst_clust3_CrockP
pricediff_lowst_clust4_Breville
pricediff_lowst_clust5_CrockP
;
run;
proc contents data = merge_finaldata;
run;

proc sort data = merge_finaldata NODUPREC out=merge_finaldata1 ;
by  TIME1 ;
run;
proc expand data=merge_finaldata1 out=merge_finaldata2 method = none; 
convert	Y_amzn_breville	=	Y_amzn_breville_lag1	/transformout=(lag 1);
convert	Y_amzn_breville	=	Y_amzn_breville_lag2	/transformout=(lag 2);
convert	Y_amzn_breville	=	Y_amzn_breville_lag3	/transformout=(lag 3);
convert	Y_amzn_cosori	=	Y_amzn_cosori_lag1	/transformout=(lag 1);
convert	Y_amzn_cosori	=	Y_amzn_cosori_lag2	/transformout=(lag 2);
convert	Y_amzn_cosori	=	Y_amzn_cosori_lag3	/transformout=(lag 3);
convert	Y_amzn_ep	=	Y_amzn_ep_lag1	/transformout=(lag 1);
convert	Y_amzn_ep	=	Y_amzn_ep_lag2	/transformout=(lag 2);
convert	Y_amzn_ep	=	Y_amzn_ep_lag3	/transformout=(lag 3);
convert	Y_amzn_ip	=	Y_amzn_ip_lag1	/transformout=(lag 1);
convert	Y_amzn_ip	=	Y_amzn_ip_lag2	/transformout=(lag 2);
convert	Y_amzn_ip	=	Y_amzn_ip_lag3	/transformout=(lag 3);
convert	Y_amzn_crockp	=	Y_amzn_crockp_lag1	/transformout=(lag 1);
convert	Y_amzn_crockp	=	Y_amzn_crockp_lag2	/transformout=(lag 2);
convert	Y_amzn_crockp	=	Y_amzn_crockp_lag3	/transformout=(lag 3);
convert	Y_clust1_breville	=	Y_clust1_breville_lag1	/transformout=(lag 1);
convert	Y_clust1_breville	=	Y_clust1_breville_lag2	/transformout=(lag 2);
convert	Y_clust1_breville	=	Y_clust1_breville_lag3	/transformout=(lag 3);
convert	Y_clust2_ep	=	Y_clust2_ep_lag1	/transformout=(lag 1);
convert	Y_clust2_ep	=	Y_clust2_ep_lag2	/transformout=(lag 2);
convert	Y_clust2_ep	=	Y_clust2_ep_lag3	/transformout=(lag 3);
convert	Y_clust3_crockp	=	Y_clust3_crockp_lag1	/transformout=(lag 1);
convert	Y_clust3_crockp	=	Y_clust3_crockp_lag2	/transformout=(lag 2);
convert	Y_clust3_crockp	=	Y_clust3_crockp_lag3	/transformout=(lag 3);
convert	Y_clust4_breville	=	Y_clust4_breville_lag1	/transformout=(lag 1);
convert	Y_clust4_breville	=	Y_clust4_breville_lag2	/transformout=(lag 2);
convert	Y_clust4_breville	=	Y_clust4_breville_lag3	/transformout=(lag 3);
convert	Y_clust5_crockp	=	Y_clust5_crockp_lag1	/transformout=(lag 1);
convert	Y_clust5_crockp	=	Y_clust5_crockp_lag2	/transformout=(lag 2);
convert	Y_clust5_crockp	=	Y_clust5_crockp_lag3	/transformout=(lag 3);
convert	Answered_Questionsavbay	=	Answered_Questionsavbay_lag1	/transformout = (lag 1);
convert	Answered_Questionsbreville	=	Answered_Questionsbrv_lag1	/transformout = (lag 1);
convert	Answered_Questionscosori	=	Answered_Questionscosori_lag1	/transformout = (lag 1);
convert	Answered_Questionscrockp	=	Answered_Questionscrockp_lag1	/transformout = (lag 1);
convert	Answered_Questionscuisinart	=	Answered_Questionscnart_lag1	/transformout = (lag 1);
convert	Answered_Questionscusimax	=	Answered_Questionscmax_lag1	/transformout = (lag 1);
convert	Answered_Questionselechms	=	Answered_Questionselec_lag1	/transformout = (lag 1);
convert	Answered_Questionsgkchf	=	Answered_Questionsgkchf_lag1	/transformout = (lag 1);
convert	Answered_Questionshbeach	=	Answered_Questionshbeach_lag1	/transformout = (lag 1);
convert	Answered_Questionsmmill	=	Answered_Questionsmmill_lag1	/transformout = (lag 1);
convert	Answered_Questionsoyama	=	Answered_Questionsoyama_lag1	/transformout = (lag 1);
convert	Answered_Questionsppro	=	Answered_Questionsppro_lag1	/transformout = (lag 1);
convert	FBA_used3p	=	FBA_used3p_lag1	/transformout = (lag 1);
convert	Freeship_amzn	=	Freeship_amzn_lag1	/transformout = (lag 1);
convert prime_amzn = prime_amzn_lag1 /transformout = (lag 1);
convert	Freeship_used3p	=	Freeship_used3p_lag1	/transformout = (lag 1);
convert prime_used3p = prime_used3p_lag1 /transformout = (lag 1);
convert	Freeship_usedamzn	=	Freeship_usedamzn_lag1	/transformout = (lag 1);
convert	NUM_IPamzn	=	NUM_IPamzn_lag1	/transformout = (lag 1);
convert	NUM_IPclust1	=	NUM_IPclust1_lag1	/transformout = (lag 1);
convert	NUM_IPclust2	=	NUM_IPclust2_lag1	/transformout = (lag 1);
convert	NUM_IPclust3	=	NUM_IPclust3_lag1	/transformout = (lag 1);
convert	NUM_IPclust4	=	NUM_IPclust4_lag1	/transformout = (lag 1);
convert	NUM_IPclust5	=	NUM_IPclust5_lag1	/transformout = (lag 1);
convert	NUM_IPused3p	=	NUM_IPused3p_lag1	/transformout = (lag 1);
convert	NUM_IPusedamzn	=	NUM_IPusedamzn_lag1	/transformout = (lag 1);
convert	NUM_SELLERSamzn	=	NUM_SELLERSamzn_lag1	/transformout = (lag 1);
convert	NUM_SELLERSclust1	=	NUM_SELLERSclust1_lag1	/transformout = (lag 1);
convert	NUM_SELLERSclust2	=	NUM_SELLERSclust2_lag1	/transformout = (lag 1);
convert	NUM_SELLERSclust3	=	NUM_SELLERSclust3_lag1	/transformout = (lag 1);
convert	NUM_SELLERSclust4	=	NUM_SELLERSclust4_lag1	/transformout = (lag 1);
convert	NUM_SELLERSclust5	=	NUM_SELLERSclust5_lag1	/transformout = (lag 1);
convert	NUM_SELLERSused3p	=	NUM_SELLERSused3p_lag1	/transformout = (lag 1);
convert	NUM_SELLERSusedamzn	=	NUM_SELLERSusedamzn_lag1	/transformout = (lag 1);
convert	NUM_SKUSamzn	=	NUM_SKUSamzn_lag1	/transformout = (lag 1);
convert	NUM_SKUSclust1	=	NUM_SKUSclust1_lag1	/transformout = (lag 1);
convert	NUM_SKUSclust2	=	NUM_SKUSclust2_lag1	/transformout = (lag 1);
convert	NUM_SKUSclust3	=	NUM_SKUSclust3_lag1	/transformout = (lag 1);
convert	NUM_SKUSclust4	=	NUM_SKUSclust4_lag1	/transformout = (lag 1);
convert	NUM_SKUSclust5	=	NUM_SKUSclust5_lag1	/transformout = (lag 1);
convert	NUM_SKUSused3p	=	NUM_SKUSused3p_lag1	/transformout = (lag 1);
convert	NUM_SKUSusedamzn	=	NUM_SKUSusedamzn_lag1	/transformout = (lag 1);
convert num_prime_clust1 = num_prime_clust1_lag1 /transformout = (lag 1);
convert num_prime_clust2 = num_prime_clust2_lag1 /transformout = (lag 1);
convert num_prime_clust3 = num_prime_clust3_lag1 /transformout = (lag 1);
convert num_prime_clust4 = num_prime_clust4_lag1 /transformout = (lag 1);
convert num_prime_clust5 = num_prime_clust5_lag1 /transformout = (lag 1);
convert priceAmzn_Breville =  priceAmzn_Brev_lag /transformout = (lag 1);
convert priceAmzn_Cosori = priceAmzn_csri_lag   /transformout = (lag 1);
convert priceAmzn_EP = priceAmzn_EP_lag   /transformout = (lag 1);
convert priceAmzn_IP = priceAmzn_IP_lag   /transformout = (lag 1);
convert priceAmzn_CrockP = priceAmzn_CrockP_lag   /transformout = (lag 1);
convert priceclust1_Breville = priceclust1_Brev_lag   /transformout = (lag 1);
convert priceclust2_EP = priceclust2_EP_lag   /transformout = (lag 1);
convert priceclust3_CrockP = priceclust3_CrockP_lag   /transformout = (lag 1);
convert priceclust4_Breville = priceclust4_Brev_lag   /transformout = (lag 1);
convert priceclust5_CrockP = priceclust5_CrockP_lag  /transformout = (lag 1);
convert pricediff_lowst_Amzn_Breville = lagdiff_Amznbrev  /transformout = (lag 1);
convert pricediff_lowst_Amzn_Cosori = lagdiff_Amzncsri   /transformout = (lag 1);
convert pricediff_lowst_Amzn_EP = lagdiff_AmznEP   /transformout = (lag 1);
convert pricediff_lowst_Amzn_IP = lagdiff_AmznIP   /transformout = (lag 1);
convert pricediff_lowst_Amzn_CrockP = lagdiff_AmznCrockP   /transformout = (lag 1);
convert pricediff_lowst_clust1_Breville = lagdiff_clust1brev   /transformout = (lag 1);
convert pricediff_lowst_clust2_EP = lagdiff_clust2EP   /transformout = (lag 1);
convert pricediff_lowst_clust3_CrockP = lagdiff_clust3CrockP   /transformout = (lag 1);
convert pricediff_lowst_clust4_Breville = lagdiff_clust4brev  /transformout = (lag 1); 
convert pricediff_lowst_clust5_CrockP =  lagdiff_clust5CrockP     /transformout = (lag 1);
convert	Product_reviews_EP	=	Product_reviews_EP_lag1	/transformout = (lag 1);
convert	Product_reviews_avbay	=	Product_reviews_avbay_lag1	/transformout = (lag 1);
convert	Product_reviews_breville	=	Product_reviews_brv_lag1	/transformout = (lag 1);
convert	Product_reviews_cosori	=	Product_reviews_cosori_lag1	/transformout = (lag 1);
convert	Product_reviews_crockp	=	Product_reviews_crockp_lag1	/transformout = (lag 1);
convert	Product_reviews_cuisinart	=	Product_reviews_cnart_lag1	/transformout = (lag 1);
convert	Product_reviews_cusimax	=	Product_reviews_cmax_lag1	/transformout = (lag 1);
convert	Product_reviews_elechms	=	Product_reviews_elec_lag1	/transformout = (lag 1);
convert	Product_reviews_gkchf	=	Product_reviews_gkchf_lag1	/transformout = (lag 1);
convert	Product_reviews_hbeach	=	Product_reviews_hbeach_lag1	/transformout = (lag 1);
convert	Product_reviews_mmill	=	Product_reviews_mmill_lag1	/transformout = (lag 1);
convert	Product_reviews_oyama	=	Product_reviews_oyama_lag1	/transformout = (lag 1);
convert	Product_reviews_ppro	=	Product_reviews_ppro_lag1	/transformout = (lag 1);
convert	Sales_Rank_Own_Categ_EP	=	salesrank_cat_EP_lag1	/transformout = (lag 1);
convert	Sales_Rank_Own_Categ_avbay	=	salesrank_cat_avbay_lag1	/transformout = (lag 1);
convert	Sales_Rank_Own_Categ_breville	=	salesrank_cat_brv_lag1	/transformout = (lag 1);
convert	Sales_Rank_Own_Categ_cosori	=	salesrank_cat_cosori_lag1	/transformout = (lag 1);
convert	Sales_Rank_Own_Categ_crockp	=	salesrank_cat_crockp_lag1	/transformout = (lag 1);
convert	Sales_Rank_Own_Categ_cuisinart	=	salesrank_cat_cnart_lag1	/transformout = (lag 1);
convert	Sales_Rank_Own_Categ_cusimax	=	salesrank_cat_cmax_lag1	/transformout = (lag 1);
convert	Sales_Rank_Own_Categ_elechms	=	salesrank_cat_elec_lag1	/transformout = (lag 1);
convert	Sales_Rank_Own_Categ_gkchf	=	salesrank_cat_gkchf_lag1	/transformout = (lag 1);
convert	Sales_Rank_Own_Categ_hbeach	=	salesrank_cat_hbeach_lag1	/transformout = (lag 1);
convert	Sales_Rank_Own_Categ_mmill	=	salesrank_cat_mmill_lag1	/transformout = (lag 1);
convert	Sales_Rank_Own_Categ_oyama	=	salesrank_cat_oyama_lag1	/transformout = (lag 1);
convert	Sales_Rank_Own_Categ_ppro	=	salesrank_cat_ppro_lag1	/transformout = (lag 1);
convert	Sales_Rank_Sub_categ_EP	=	salesrank_subcat_EP_lag1	/transformout = (lag 1);
convert	Sales_Rank_Sub_categ_avbay	=	salesrank_subcat_avbay_lag1	/transformout = (lag 1);
convert	Sales_Rank_Sub_categ_breville	=	salesrank_subcat_brv_lag1	/transformout = (lag 1);
convert	Sales_Rank_Sub_categ_cosori	=	salesrank_subcat_cosori_lag1	/transformout = (lag 1);
convert	Sales_Rank_Sub_categ_crockp	=	salesrank_subcat_crockp_lag1	/transformout = (lag 1);
convert	Sales_Rank_Sub_categ_cuisinart	=	salesrank_subcat_cnart_lag1	/transformout = (lag 1);
convert	Sales_Rank_Sub_categ_cusimax	=	salesrank_subcat_cmax_lag1	/transformout = (lag 1);
convert	Sales_Rank_Sub_categ_elechms	=	salesrank_subcat_elec_lag1	/transformout = (lag 1);
convert	Sales_Rank_Sub_categ_gkchf	=	salesrank_subcat_gkchf_lag1	/transformout = (lag 1);
convert	Sales_Rank_Sub_categ_hbeach	=	salesrank_subcat_hbeach_lag1	/transformout = (lag 1);
convert	Sales_Rank_Sub_categ_mmill	=	salesrank_subcat_mmill_lag1	/transformout = (lag 1);
convert	Sales_Rank_Sub_categ_oyama	=	salesrank_subcat_oyama_lag1	/transformout = (lag 1);
convert	Sales_Rank_Sub_categ_ppro	=	salesrank_subcat_ppro_lag1	/transformout = (lag 1);
convert	buyboxprice_EP	=	buyboxprice_EP_lag1	/transformout = (lag 1);
convert	buyboxprice_avbay	=	buyboxprice_avbay_lag1	/transformout = (lag 1);
convert	buyboxprice_breville	=	buyboxprice_brv_lag1	/transformout = (lag 1);
convert	buyboxprice_cosori	=	buyboxprice_cosori_lag1	/transformout = (lag 1);
convert	buyboxprice_crockp	=	buyboxprice_crockp_lag1	/transformout = (lag 1);
convert	buyboxprice_cuisinart	=	buyboxprice_cnart_lag1	/transformout = (lag 1);
convert	buyboxprice_cusimax	=	buyboxprice_cmax_lag1	/transformout = (lag 1);
convert	buyboxprice_elechms	=	buyboxprice_elec_lag1	/transformout = (lag 1);
convert	buyboxprice_gkchf	=	buyboxprice_gkchf_lag1	/transformout = (lag 1);
convert	buyboxprice_hbeach	=	buyboxprice_hbeach_lag1	/transformout = (lag 1);
convert	buyboxprice_mmill	=	buyboxprice_mmill_lag1	/transformout = (lag 1);
convert	buyboxprice_oyama	=	buyboxprice_oyama_lag1	/transformout = (lag 1);
convert	buyboxprice_ppro	=	buyboxprice_ppro_lag1	/transformout = (lag 1);
convert max_nonbuybox3p_price_IP = maxnonbbox3pprice_IP_lag /transformout = (lag 1);
convert mean_nonbuybox3p_price_IP = meannonbbox3pprice_IP_lag /transformout = (lag 1);
convert min_nonbuybox3p_price_IP = minnonbbox3pprice_IP_lag /transformout = (lag 1);

convert nonbuybox3p_price_EP =  nonbbox3pprice_EP_lag /transformout = (lag 1);
convert nonbuybox3p_price_avbay = nonbbox3pprice_avbay_lag /transformout = (lag 1);
convert nonbuybox3p_price_breville = nonbbox3pprice_brev_lag /transformout = (lag 1);  
convert nonbuybox3p_price_cosori =  nonbbox3pprice_csri_lag /transformout = (lag 1);
convert nonbuybox3p_price_crockp = nonbbox3pprice_crockp_lag  /transformout = (lag 1);
convert nonbuybox3p_price_cuisinart = nonbbox3pprice_csn_lag /transformout = (lag 1);  
convert nonbuybox3p_price_cusimax =  nonbbox3pprice_cmax_lag  /transformout = (lag 1);
convert nonbuybox3p_price_elechms =  nonbbox3pprice_elec_lag /transformout = (lag 1);
convert nonbuybox3p_price_gkchf =  nonbbox3pprice_gkchf_lag  /transformout = (lag 1);
convert nonbuybox3p_price_hbeach = nonbbox3pprice_hbeach_lag  /transformout = (lag 1);
convert nonbuybox3p_price_mmill =  nonbbox3pprice_mmill_lag /transformout = (lag 1);
convert nonbuybox3p_price_oyama = nonbbox3pprice_oyama_lag  /transformout = (lag 1);
convert nonbuybox3p_price_ppro = nonbbox3pprice_ppro_lag /transformout = (lag 1);

convert	cumsalefreq_amzn	=	cumsalefreq_amzn_lag1	/transformout = (lag 1);
convert	max_Product_reviews_IP	=	max_Product_reviews_IP_lag1	/transformout = (lag 1);
convert	max_ans_questions_IP	=	max_ans_questions_IP_lag1	/transformout = (lag 1);
convert	max_buyboxprice_IP	=	max_buyboxprice_IP_lag1	/transformout = (lag 1);
convert	max_cumsalefreq_clust1	=	max_cumsalefreq_clust1_lag1	/transformout = (lag 1);
convert	max_cumsalefreq_clust2	=	max_cumsalefreq_clust2_lag1	/transformout = (lag 1);
convert	max_cumsalefreq_clust3	=	max_cumsalefreq_clust3_lag1	/transformout = (lag 1);
convert	max_cumsalefreq_clust4	=	max_cumsalefreq_clust4_lag1	/transformout = (lag 1);
convert	max_cumsalefreq_clust5	=	max_cumsalefreq_clust5_lag1	/transformout = (lag 1);
convert	max_num_seller_rating_clust1	=	max_num_sellerate_clust1_lag1	/transformout = (lag 1);
convert	max_num_seller_rating_clust2	=	max_num_sellerate_clust2_lag1	/transformout = (lag 1);
convert	max_num_seller_rating_clust3	=	max_num_sellerate_clust3_lag1	/transformout = (lag 1);
convert	max_num_seller_rating_clust4	=	max_num_sellerate_clust4_lag1	/transformout = (lag 1);
convert	max_num_seller_rating_clust5	=	max_num_sellerate_clust5_lag1	/transformout = (lag 1);
convert	max_perct_sellerrating_clust1	=	max_PerctSellrate_clust1_lag1	/transformout = (lag 1);
convert	max_perct_sellerrating_clust2	=	max_PerctSellrate_clust2_lag1	/transformout = (lag 1);
convert	max_perct_sellerrating_clust3	=	max_PerctSellrate_clust3_lag1	/transformout = (lag 1);
convert	max_perct_sellerrating_clust4	=	max_PerctSellrate_clust4_lag1	/transformout = (lag 1);
convert	max_perct_sellerrating_clust5	=	max_PerctSellrate_clust5_lag1	/transformout = (lag 1);
convert	max_product_star_rate_IP	=	max_product_star_rate_IP_lag1	/transformout = (lag 1);
convert	max_salesrank_categ_IP	=	max_salesrank_categ_IP_lag1	/transformout = (lag 1);
convert	max_salesrank_subcateg_IP	=	max_salesrank_subcat_IP_lag1	/transformout = (lag 1);
convert	max_seller_star_rating_clust1	=	max_sellerstar_clust1_lag1	/transformout = (lag 1);
convert	max_seller_star_rating_clust2	=	max_sellerstar_clust2_lag1	/transformout = (lag 1);
convert	max_seller_star_rating_clust3	=	max_sellerstar_clust3_lag1	/transformout = (lag 1);
convert	max_seller_star_rating_clust4	=	max_sellerstar_clust4_lag1	/transformout = (lag 1);
convert	max_seller_star_rating_clust5	=	max_sellerstar_clust5_lag1	/transformout = (lag 1);
convert	max_uniq_ASIN_clust1	=	max_uniq_ASIN_clust1_lag1	/transformout = (lag 1);
convert	max_uniq_ASIN_clust2	=	max_uniq_ASIN_clust2_lag1	/transformout = (lag 1);
convert	max_uniq_ASIN_clust3	=	max_uniq_ASIN_clust3_lag1	/transformout = (lag 1);
convert	max_uniq_ASIN_clust4	=	max_uniq_ASIN_clust4_lag1	/transformout = (lag 1);
convert	max_uniq_ASIN_clust5	=	max_uniq_ASIN_clust5_lag1	/transformout = (lag 1);
convert	mean_Product_reviews_IP	=	mean_Product_reviews_IP_lag1	/transformout = (lag 1);
convert	mean_ans_questions_IP	=	mean_ans_questions_IP_lag1	/transformout = (lag 1);
convert	mean_buyboxprice_IP	=	mean_buyboxprice_IP_lag1	/transformout = (lag 1);
convert	mean_cumsalefreq_clust1	=	mean_cumsalefreq_clust1_lag1	/transformout = (lag 1);
convert	mean_cumsalefreq_clust2	=	mean_cumsalefreq_clust2_lag1	/transformout = (lag 1);
convert	mean_cumsalefreq_clust3	=	mean_cumsalefreq_clust3_lag1	/transformout = (lag 1);
convert	mean_cumsalefreq_clust4	=	mean_cumsalefreq_clust4_lag1	/transformout = (lag 1);
convert	mean_cumsalefreq_clust5	=	mean_cumsalefreq_clust5_lag1	/transformout = (lag 1);
convert	mean_num_seller_rating_clust1	=	mean_num_sellerate_clust1_lag1	/transformout = (lag 1);
convert	mean_num_seller_rating_clust2	=	mean_num_sellerate_clust2_lag1	/transformout = (lag 1);
convert	mean_num_seller_rating_clust3	=	mean_num_sellerate_clust3_lag1	/transformout = (lag 1);
convert	mean_num_seller_rating_clust4	=	mean_num_sellerate_clust4_lag1	/transformout = (lag 1);
convert	mean_num_seller_rating_clust5	=	mean_num_sellerate_clust5_lag1	/transformout = (lag 1);
convert	mean_perct_sellerrating_clust1	=	mean_PerctSellrate_clust1_lag1	/transformout = (lag 1);
convert	mean_perct_sellerrating_clust2	=	mean_PerctSellrate_clust2_lag1	/transformout = (lag 1);
convert	mean_perct_sellerrating_clust3	=	mean_PerctSellrate_clust3_lag1	/transformout = (lag 1);
convert	mean_perct_sellerrating_clust4	=	mean_PerctSellrate_clust4_lag1	/transformout = (lag 1);
convert	mean_perct_sellerrating_clust5	=	mean_PerctSellrate_clust5_lag1	/transformout = (lag 1);
convert	mean_product_star_rate_IP	=	mean_product_star_rate_IP_lag1	/transformout = (lag 1);
convert	mean_salesrank_categ_IP	=	mean_salesrank_categ_IP_lag1	/transformout = (lag 1);
convert	mean_salesrank_subcateg_IP	=	mean_salesrank_subcat_IP_lag1	/transformout = (lag 1);
convert	mean_seller_star_rating_clust1	=	mean_sellerstar_clust1_lag1	/transformout = (lag 1);
convert	mean_seller_star_rating_clust2	=	mean_sellerstar_clust2_lag1	/transformout = (lag 1);
convert	mean_seller_star_rating_clust3	=	mean_sellerstar_clust3_lag1	/transformout = (lag 1);
convert	mean_seller_star_rating_clust4	=	mean_sellerstar_clust4_lag1	/transformout = (lag 1);
convert	mean_seller_star_rating_clust5	=	mean_sellerstar_clust5_lag1	/transformout = (lag 1);
convert	mean_uniq_ASIN_clust1	=	mean_uniq_ASIN_clust1_lag1	/transformout = (lag 1);
convert	mean_uniq_ASIN_clust2	=	mean_uniq_ASIN_clust2_lag1	/transformout = (lag 1);
convert	mean_uniq_ASIN_clust3	=	mean_uniq_ASIN_clust3_lag1	/transformout = (lag 1);
convert	mean_uniq_ASIN_clust4	=	mean_uniq_ASIN_clust4_lag1	/transformout = (lag 1);
convert	mean_uniq_ASIN_clust5	=	mean_uniq_ASIN_clust5_lag1	/transformout = (lag 1);
convert	min_Product_reviews_IP	=	min_Product_reviews_IP_lag1	/transformout = (lag 1);
convert	min_ans_questions_IP	=	min_ans_questions_IP_lag1	/transformout = (lag 1);
convert	min_buyboxprice_IP	=	min_buyboxprice_IP_lag1	/transformout = (lag 1);
convert	min_cumsalefreq_clust1	=	min_cumsalefreq_clust1_lag1	/transformout = (lag 1);
convert	min_cumsalefreq_clust2	=	min_cumsalefreq_clust2_lag1	/transformout = (lag 1);
convert	min_cumsalefreq_clust3	=	min_cumsalefreq_clust3_lag1	/transformout = (lag 1);
convert	min_cumsalefreq_clust4	=	min_cumsalefreq_clust4_lag1	/transformout = (lag 1);
convert	min_cumsalefreq_clust5	=	min_cumsalefreq_clust5_lag1	/transformout = (lag 1);
convert	min_num_seller_rating_clust1	=	min_num_sellerate_clust1_lag1	/transformout = (lag 1);
convert	min_num_seller_rating_clust2	=	min_num_sellerate_clust2_lag1	/transformout = (lag 1);
convert	min_num_seller_rating_clust3	=	min_num_sellerate_clust3_lag1	/transformout = (lag 1);
convert	min_num_seller_rating_clust4	=	min_num_sellerate_clust4_lag1	/transformout = (lag 1);
convert	min_num_seller_rating_clust5	=	min_num_sellerate_clust5_lag1	/transformout = (lag 1);
convert	min_perct_sellerrating_clust1	=	min_PerctSellrate_clust1_lag1	/transformout = (lag 1);
convert	min_perct_sellerrating_clust2	=	min_PerctSellrate_clust2_lag1	/transformout = (lag 1);
convert	min_perct_sellerrating_clust3	=	min_PerctSellrate_clust3_lag1	/transformout = (lag 1);
convert	min_perct_sellerrating_clust4	=	min_PerctSellrate_clust4_lag1	/transformout = (lag 1);
convert	min_perct_sellerrating_clust5	=	min_PerctSellrate_clust5_lag1	/transformout = (lag 1);
convert	min_product_star_rate_IP	=	min_product_star_rate_IP_lag1	/transformout = (lag 1);
convert	min_salesrank_categ_IP	=	min_salesrank_categ_IP_lag1	/transformout = (lag 1);
convert	min_salesrank_subcateg_IP	=	min_salesrank_subcat_IP_lag1	/transformout = (lag 1);
convert	min_seller_star_rating_clust1	=	min_sellerstar_clust1_lag1	/transformout = (lag 1);
convert	min_seller_star_rating_clust2	=	min_sellerstar_clust2_lag1	/transformout = (lag 1);
convert	min_seller_star_rating_clust3	=	min_sellerstar_clust3_lag1	/transformout = (lag 1);
convert	min_seller_star_rating_clust4	=	min_sellerstar_clust4_lag1	/transformout = (lag 1);
convert	min_seller_star_rating_clust5	=	min_sellerstar_clust5_lag1	/transformout = (lag 1);
convert	min_uniq_ASIN_clust1	=	min_uniq_ASIN_clust1_lag1	/transformout = (lag 1);
convert	min_uniq_ASIN_clust2	=	min_uniq_ASIN_clust2_lag1	/transformout = (lag 1);
convert	min_uniq_ASIN_clust3	=	min_uniq_ASIN_clust3_lag1	/transformout = (lag 1);
convert	min_uniq_ASIN_clust4	=	min_uniq_ASIN_clust4_lag1	/transformout = (lag 1);
convert	min_uniq_ASIN_clust5	=	min_uniq_ASIN_clust5_lag1	/transformout = (lag 1);
convert	num_AvBayamzn	=	num_AvBayamzn_lag1	/transformout = (lag 1);
convert	num_AvBayclust1	=	num_AvBayclust1_lag1	/transformout = (lag 1);
convert	num_AvBayclust2	=	num_AvBayclust2_lag1	/transformout = (lag 1);
convert	num_AvBayclust3	=	num_AvBayclust3_lag1	/transformout = (lag 1);
convert	num_AvBayclust4	=	num_AvBayclust4_lag1	/transformout = (lag 1);
convert	num_AvBayclust5	=	num_AvBayclust5_lag1	/transformout = (lag 1);
convert	num_AvBayused3p	=	num_AvBayused3p_lag1	/transformout = (lag 1);
convert	num_AvBayusedamzn	=	num_AvBayusedamzn_lag1	/transformout = (lag 1);
convert	num_Brevilleamzn	=	num_brvamzn_lag1	/transformout = (lag 1);
convert	num_Brevilleclust1	=	num_brvclust1_lag1	/transformout = (lag 1);
convert	num_Brevilleclust2	=	num_brvclust2_lag1	/transformout = (lag 1);
convert	num_Brevilleclust3	=	num_brvclust3_lag1	/transformout = (lag 1);
convert	num_Brevilleclust4	=	num_brvclust4_lag1	/transformout = (lag 1);
convert	num_Brevilleclust5	=	num_brvclust5_lag1	/transformout = (lag 1);
convert	num_Brevilleused3p	=	num_brvused3p_lag1	/transformout = (lag 1);
convert	num_Brevilleusedamzn	=	num_brvusedamzn_lag1	/transformout = (lag 1);
convert	num_Cosoriamzn	=	num_Cosoriamzn_lag1	/transformout = (lag 1);
convert	num_Cosoriclust1	=	num_Cosoriclust1_lag1	/transformout = (lag 1);
convert	num_Cosoriclust2	=	num_Cosoriclust2_lag1	/transformout = (lag 1);
convert	num_Cosoriclust3	=	num_Cosoriclust3_lag1	/transformout = (lag 1);
convert	num_Cosoriclust4	=	num_Cosoriclust4_lag1	/transformout = (lag 1);
convert	num_Cosoriclust5	=	num_Cosoriclust5_lag1	/transformout = (lag 1);
convert	num_Cosoriused3p	=	num_Cosoriused3p_lag1	/transformout = (lag 1);
convert	num_Cosoriusedamzn	=	num_Cosoriusedamzn_lag1	/transformout = (lag 1);
convert	num_CrockPamzn	=	num_CrockPamzn_lag1	/transformout = (lag 1);
convert	num_CrockPclust1	=	num_CrockPclust1_lag1	/transformout = (lag 1);
convert	num_CrockPclust2	=	num_CrockPclust2_lag1	/transformout = (lag 1);
convert	num_CrockPclust3	=	num_CrockPclust3_lag1	/transformout = (lag 1);
convert	num_CrockPclust4	=	num_CrockPclust4_lag1	/transformout = (lag 1);
convert	num_CrockPclust5	=	num_CrockPclust5_lag1	/transformout = (lag 1);
convert	num_CrockPused3p	=	num_CrockPused3p_lag1	/transformout = (lag 1);
convert	num_CrockPusedamzn	=	num_CrockPusedamzn_lag1	/transformout = (lag 1);
convert	num_Cuisinartamzn	=	num_cnartamzn_lag1	/transformout = (lag 1);
convert	num_Cuisinartclust1	=	num_cnartclust1_lag1	/transformout = (lag 1);
convert	num_Cuisinartclust2	=	num_cnartclust2_lag1	/transformout = (lag 1);
convert	num_Cuisinartclust3	=	num_cnartclust3_lag1	/transformout = (lag 1);
convert	num_Cuisinartclust4	=	num_cnartclust4_lag1	/transformout = (lag 1);
convert	num_Cuisinartclust5	=	num_cnartclust5_lag1	/transformout = (lag 1);
convert	num_Cuisinartused3p	=	num_cnartused3p_lag1	/transformout = (lag 1);
convert	num_Cuisinartusedamzn	=	num_cnartusedamzn_lag1	/transformout = (lag 1);
convert	num_Cusimaxamzn	=	num_cmaxamzn_lag1	/transformout = (lag 1);
convert	num_Cusimaxclust1	=	num_cmaxclust1_lag1	/transformout = (lag 1);
convert	num_Cusimaxclust2	=	num_cmaxclust2_lag1	/transformout = (lag 1);
convert	num_Cusimaxclust3	=	num_cmaxclust3_lag1	/transformout = (lag 1);
convert	num_Cusimaxclust4	=	num_cmaxclust4_lag1	/transformout = (lag 1);
convert	num_Cusimaxclust5	=	num_cmaxclust5_lag1	/transformout = (lag 1);
convert	num_Cusimaxused3p	=	num_cmaxused3p_lag1	/transformout = (lag 1);
convert	num_Cusimaxusedamzn	=	num_cmaxusedamzn_lag1	/transformout = (lag 1);
convert	num_EPamzn	=	num_EPamzn_lag1	/transformout = (lag 1);
convert	num_EPclust1	=	num_EPclust1_lag1	/transformout = (lag 1);
convert	num_EPclust2	=	num_EPclust2_lag1	/transformout = (lag 1);
convert	num_EPclust3	=	num_EPclust3_lag1	/transformout = (lag 1);
convert	num_EPclust4	=	num_EPclust4_lag1	/transformout = (lag 1);
convert	num_EPclust5	=	num_EPclust5_lag1	/transformout = (lag 1);
convert	num_EPused3p	=	num_EPused3p_lag1	/transformout = (lag 1);
convert	num_EPusedamzn	=	num_EPusedamzn_lag1	/transformout = (lag 1);
convert	num_Elechmsamzn	=	num_elecamzn_lag1	/transformout = (lag 1);
convert	num_Elechmsclust1	=	num_elecclust1_lag1	/transformout = (lag 1);
convert	num_Elechmsclust2	=	num_elecclust2_lag1	/transformout = (lag 1);
convert	num_Elechmsclust3	=	num_elecclust3_lag1	/transformout = (lag 1);
convert	num_Elechmsclust4	=	num_elecclust4_lag1	/transformout = (lag 1);
convert	num_Elechmsclust5	=	num_elecclust5_lag1	/transformout = (lag 1);
convert	num_Elechmsused3p	=	num_elecused3p_lag1	/transformout = (lag 1);
convert	num_Elechmsusedamzn	=	num_elecusedamzn_lag1	/transformout = (lag 1);
convert	num_FBA_clust1	=	num_FBA_clust1_lag1	/transformout = (lag 1);
convert	num_FBA_clust2	=	num_FBA_clust2_lag1	/transformout = (lag 1);
convert	num_FBA_clust3	=	num_FBA_clust3_lag1	/transformout = (lag 1);
convert	num_FBA_clust4	=	num_FBA_clust4_lag1	/transformout = (lag 1);
convert	num_FBA_clust5	=	num_FBA_clust5_lag1	/transformout = (lag 1);
convert	num_Freeship_clust1	=	num_Freeship_clust1_lag1	/transformout = (lag 1);
convert	num_Freeship_clust2	=	num_Freeship_clust2_lag1	/transformout = (lag 1);
convert	num_Freeship_clust3	=	num_Freeship_clust3_lag1	/transformout = (lag 1);
convert	num_Freeship_clust4	=	num_Freeship_clust4_lag1	/transformout = (lag 1);
convert	num_Freeship_clust5	=	num_Freeship_clust5_lag1	/transformout = (lag 1);
convert	num_Gkchefamzn	=	num_Gkchefamzn_lag1	/transformout = (lag 1);
convert	num_Gkchefclust1	=	num_Gkchefclust1_lag1	/transformout = (lag 1);
convert	num_Gkchefclust2	=	num_Gkchefclust2_lag1	/transformout = (lag 1);
convert	num_Gkchefclust3	=	num_Gkchefclust3_lag1	/transformout = (lag 1);
convert	num_Gkchefclust4	=	num_Gkchefclust4_lag1	/transformout = (lag 1);
convert	num_Gkchefclust5	=	num_Gkchefclust5_lag1	/transformout = (lag 1);
convert	num_Gkchefused3p	=	num_Gkchefused3p_lag1	/transformout = (lag 1);
convert	num_Gkchefusedamzn	=	num_Gkchefusedamzn_lag1	/transformout = (lag 1);
convert	num_HBeachamzn	=	num_HBeachamzn_lag1	/transformout = (lag 1);
convert	num_HBeachclust1	=	num_HBeachclust1_lag1	/transformout = (lag 1);
convert	num_HBeachclust2	=	num_HBeachclust2_lag1	/transformout = (lag 1);
convert	num_HBeachclust3	=	num_HBeachclust3_lag1	/transformout = (lag 1);
convert	num_HBeachclust4	=	num_HBeachclust4_lag1	/transformout = (lag 1);
convert	num_HBeachclust5	=	num_HBeachclust5_lag1	/transformout = (lag 1);
convert	num_HBeachused3p	=	num_HBeachused3p_lag1	/transformout = (lag 1);
convert	num_HBeachusedamzn	=	num_HBeachusedamzn_lag1	/transformout = (lag 1);
convert	num_MMillamzn	=	num_MMillamzn_lag1	/transformout = (lag 1);
convert	num_MMillclust1	=	num_MMillclust1_lag1	/transformout = (lag 1);
convert	num_MMillclust2	=	num_MMillclust2_lag1	/transformout = (lag 1);
convert	num_MMillclust3	=	num_MMillclust3_lag1	/transformout = (lag 1);
convert	num_MMillclust4	=	num_MMillclust4_lag1	/transformout = (lag 1);
convert	num_MMillclust5	=	num_MMillclust5_lag1	/transformout = (lag 1);
convert	num_MMillused3p	=	num_MMillused3p_lag1	/transformout = (lag 1);
convert	num_MMillusedamzn	=	num_MMillusedamzn_lag1	/transformout = (lag 1);
convert	num_Oyamaamzn	=	num_Oyamaamzn_lag1	/transformout = (lag 1);
convert	num_Oyamaclust1	=	num_Oyamaclust1_lag1	/transformout = (lag 1);
convert	num_Oyamaclust2	=	num_Oyamaclust2_lag1	/transformout = (lag 1);
convert	num_Oyamaclust3	=	num_Oyamaclust3_lag1	/transformout = (lag 1);
convert	num_Oyamaclust4	=	num_Oyamaclust4_lag1	/transformout = (lag 1);
convert	num_Oyamaclust5	=	num_Oyamaclust5_lag1	/transformout = (lag 1);
convert	num_Oyamaused3p	=	num_Oyamaused3p_lag1	/transformout = (lag 1);
convert	num_Oyamausedamzn	=	num_Oyamausedamzn_lag1	/transformout = (lag 1);
convert	num_PProamzn	=	num_PProamzn_lag1	/transformout = (lag 1);
convert	num_PProclust1	=	num_PProclust1_lag1	/transformout = (lag 1);
convert	num_PProclust2	=	num_PProclust2_lag1	/transformout = (lag 1);
convert	num_PProclust3	=	num_PProclust3_lag1	/transformout = (lag 1);
convert	num_PProclust4	=	num_PProclust4_lag1	/transformout = (lag 1);
convert	num_PProclust5	=	num_PProclust5_lag1	/transformout = (lag 1);
convert	num_PProused3p	=	num_PProused3p_lag1	/transformout = (lag 1);
convert	num_PProusedamzn	=	num_PProusedamzn_lag1	/transformout = (lag 1);
convert	num_pricechangeEPamzn	=	pricechangesEPamzn_lag1	/transformout = (lag 1);
convert	num_pricechangeEPclust1	=	pricechangesEPclust1_lag1	/transformout = (lag 1);
convert	num_pricechangeEPclust2	=	pricechangesEPclust2_lag1	/transformout = (lag 1);
convert	num_pricechangeEPclust3	=	pricechangesEPclust3_lag1	/transformout = (lag 1);
convert	num_pricechangeEPclust4	=	pricechangesEPclust4_lag1	/transformout = (lag 1);
convert	num_pricechangeEPclust5	=	pricechangesEPclust5_lag1	/transformout = (lag 1);
convert	num_pricechangeEPused3p	=	pricechangesEPused3p_lag1	/transformout = (lag 1);
convert	num_pricechangeEPuseamz	=	pricechangesEPusedamzn_lag1	/transformout = (lag 1);
convert	num_pricechangeIPamzn	=	pricechangesIPamzn_lag1	/transformout = (lag 1);
convert	num_pricechangeIPclust1	=	pricechangesIPclust1_lag1	/transformout = (lag 1);
convert	num_pricechangeIPclust2	=	pricechangesIPclust2_lag1	/transformout = (lag 1);
convert	num_pricechangeIPclust3	=	pricechangesIPclust3_lag1	/transformout = (lag 1);
convert	num_pricechangeIPclust4	=	pricechangesIPclust4_lag1	/transformout = (lag 1);
convert	num_pricechangeIPclust5	=	pricechangesIPclust5_lag1	/transformout = (lag 1);
convert	num_pricechangeIPused3p	=	pricechangesIPused3p_lag1	/transformout = (lag 1);
convert	num_pricechangeIPuseamz	=	pricechangesIPusedamzn_lag1	/transformout = (lag 1);
convert	num_pricechangeavbayamzn	=	pricechangesavbayamzn_lag1	/transformout = (lag 1);
convert	num_pricechangeavbayclust1	=	pricechangesavbayclust1_lag1	/transformout = (lag 1);
convert	num_pricechangeavbayclust2	=	pricechangesavbayclust2_lag1	/transformout = (lag 1);
convert	num_pricechangeavbayclust3	=	pricechangesavbayclust3_lag1	/transformout = (lag 1);
convert	num_pricechangeavbayclust4	=	pricechangesavbayclust4_lag1	/transformout = (lag 1);
convert	num_pricechangeavbayclust5	=	pricechangesavbayclust5_lag1	/transformout = (lag 1);
convert	num_pricechangeavbayused3p	=	pricechangesavbayused3p_lag1	/transformout = (lag 1);
convert	num_pricechangeavbayuseamz	=	pricechangesavbayusedamzn_lag1	/transformout = (lag 1);
convert	num_pricechangebrevilleamzn	=	pricechangesbrvamzn_lag1	/transformout = (lag 1);
convert	num_pricechangebrevilleclust1	=	pricechangesbrvclust1_lag1	/transformout = (lag 1);
convert	num_pricechangebrevilleclust2	=	pricechangesbrvclust2_lag1	/transformout = (lag 1);
convert	num_pricechangebrevilleclust3	=	pricechangesbrvclust3_lag1	/transformout = (lag 1);
convert	num_pricechangebrevilleclust4	=	pricechangesbrvclust4_lag1	/transformout = (lag 1);
convert	num_pricechangebrevilleclust5	=	pricechangesbrvclust5_lag1	/transformout = (lag 1);
convert	num_pricechangebrevilleused3p	=	pricechangesbrvused3p_lag1	/transformout = (lag 1);
convert	num_pricechangebrevilleuseamz	=	pricechangesbrvusedamzn_lag1	/transformout = (lag 1);
convert	num_pricechangecosoriamzn	=	pricechangescosoriamzn_lag1	/transformout = (lag 1);
convert	num_pricechangecosoriclust1	=	pricechangescosoriclust1_lag1	/transformout = (lag 1);
convert	num_pricechangecosoriclust2	=	pricechangescosoriclust2_lag1	/transformout = (lag 1);
convert	num_pricechangecosoriclust3	=	pricechangescosoriclust3_lag1	/transformout = (lag 1);
convert	num_pricechangecosoriclust4	=	pricechangescosoriclust4_lag1	/transformout = (lag 1);
convert	num_pricechangecosoriclust5	=	pricechangescosoriclust5_lag1	/transformout = (lag 1);
convert	num_pricechangecosoriused3p	=	pricechangescosoriused3p_lag1	/transformout = (lag 1);
convert	num_pricechangecosoriuseamz	=	pricechangescosoriusedamzn_lag1	/transformout = (lag 1);
convert	num_pricechangecrockpamzn	=	pricechangescrockpamzn_lag1	/transformout = (lag 1);
convert	num_pricechangecrockpclust1	=	pricechangescrockpclust1_lag1	/transformout = (lag 1);
convert	num_pricechangecrockpclust2	=	pricechangescrockpclust2_lag1	/transformout = (lag 1);
convert	num_pricechangecrockpclust3	=	pricechangescrockpclust3_lag1	/transformout = (lag 1);
convert	num_pricechangecrockpclust4	=	pricechangescrockpclust4_lag1	/transformout = (lag 1);
convert	num_pricechangecrockpclust5	=	pricechangescrockpclust5_lag1	/transformout = (lag 1);
convert	num_pricechangecrockpused3p	=	pricechangescrockpused3p_lag1	/transformout = (lag 1);
convert	num_pricechangecrockpuseamz	=	pricechangescrockpusedamzn_lag1	/transformout = (lag 1);
convert	num_pricechangecuisinartamzn	=	pricechangescnartamzn_lag1	/transformout = (lag 1);
convert	num_pricechangecuisinartclust1	=	pricechangescnartclust1_lag1	/transformout = (lag 1);
convert	num_pricechangecuisinartclust2	=	pricechangescnartclust2_lag1	/transformout = (lag 1);
convert	num_pricechangecuisinartclust3	=	pricechangescnartclust3_lag1	/transformout = (lag 1);
convert	num_pricechangecuisinartclust4	=	pricechangescnartclust4_lag1	/transformout = (lag 1);
convert	num_pricechangecuisinartclust5	=	pricechangescnartclust5_lag1	/transformout = (lag 1);
convert	num_pricechangecuisinartused3p	=	pricechangescnartused3p_lag1	/transformout = (lag 1);
convert	num_pricechangecuisinartuseamz	=	pricechangescnartusedamzn_lag1	/transformout = (lag 1);
convert	num_pricechangecusimaxamzn	=	pricechangescmaxamzn_lag1	/transformout = (lag 1);
convert	num_pricechangecusimaxclust1	=	pricechangescmaxclust1_lag1	/transformout = (lag 1);
convert	num_pricechangecusimaxclust2	=	pricechangescmaxclust2_lag1	/transformout = (lag 1);
convert	num_pricechangecusimaxclust3	=	pricechangescmaxclust3_lag1	/transformout = (lag 1);
convert	num_pricechangecusimaxclust4	=	pricechangescmaxclust4_lag1	/transformout = (lag 1);
convert	num_pricechangecusimaxclust5	=	pricechangescmaxclust5_lag1	/transformout = (lag 1);
convert	num_pricechangecusimaxused3p	=	pricechangescmaxused3p_lag1	/transformout = (lag 1);
convert	num_pricechangecusimaxuseamz	=	pricechangescmaxusedamzn_lag1	/transformout = (lag 1);
convert	num_pricechangeelechmsamzn	=	pricechangeselecamzn_lag1	/transformout = (lag 1);
convert	num_pricechangeelechmsclust1	=	pricechangeselecclust1_lag1	/transformout = (lag 1);
convert	num_pricechangeelechmsclust2	=	pricechangeselecclust2_lag1	/transformout = (lag 1);
convert	num_pricechangeelechmsclust3	=	pricechangeselecclust3_lag1	/transformout = (lag 1);
convert	num_pricechangeelechmsclust4	=	pricechangeselecclust4_lag1	/transformout = (lag 1);
convert	num_pricechangeelechmsclust5	=	pricechangeselecclust5_lag1	/transformout = (lag 1);
convert	num_pricechangeelechmsused3p	=	pricechangeselecused3p_lag1	/transformout = (lag 1);
convert	num_pricechangeelechmsuseamz	=	pricechangeselecusedamzn_lag1	/transformout = (lag 1);
convert	num_pricechangegkchfamzn	=	pricechangesgkchfamzn_lag1	/transformout = (lag 1);
convert	num_pricechangegkchfclust1	=	pricechangesgkchfclust1_lag1	/transformout = (lag 1);
convert	num_pricechangegkchfclust2	=	pricechangesgkchfclust2_lag1	/transformout = (lag 1);
convert	num_pricechangegkchfclust3	=	pricechangesgkchfclust3_lag1	/transformout = (lag 1);
convert	num_pricechangegkchfclust4	=	pricechangesgkchfclust4_lag1	/transformout = (lag 1);
convert	num_pricechangegkchfclust5	=	pricechangesgkchfclust5_lag1	/transformout = (lag 1);
convert	num_pricechangegkchfused3p	=	pricechangesgkchfused3p_lag1	/transformout = (lag 1);
convert	num_pricechangegkchfuseamz	=	pricechangesgkchfusedamzn_lag1	/transformout = (lag 1);
convert	num_pricechangehbeachamzn	=	pricechangeshbeachamzn_lag1	/transformout = (lag 1);
convert	num_pricechangehbeachclust1	=	pricechangeshbeachclust1_lag1	/transformout = (lag 1);
convert	num_pricechangehbeachclust2	=	pricechangeshbeachclust2_lag1	/transformout = (lag 1);
convert	num_pricechangehbeachclust3	=	pricechangeshbeachclust3_lag1	/transformout = (lag 1);
convert	num_pricechangehbeachclust4	=	pricechangeshbeachclust4_lag1	/transformout = (lag 1);
convert	num_pricechangehbeachclust5	=	pricechangeshbeachclust5_lag1	/transformout = (lag 1);
convert	num_pricechangehbeachused3p	=	pricechangeshbeachused3p_lag1	/transformout = (lag 1);
convert	num_pricechangehbeachuseamz	=	pricechangeshbeachusedamzn_lag1	/transformout = (lag 1);
convert	num_pricechangemmillamzn	=	pricechangesmmillamzn_lag1	/transformout = (lag 1);
convert	num_pricechangemmillclust1	=	pricechangesmmillclust1_lag1	/transformout = (lag 1);
convert	num_pricechangemmillclust2	=	pricechangesmmillclust2_lag1	/transformout = (lag 1);
convert	num_pricechangemmillclust3	=	pricechangesmmillclust3_lag1	/transformout = (lag 1);
convert	num_pricechangemmillclust4	=	pricechangesmmillclust4_lag1	/transformout = (lag 1);
convert	num_pricechangemmillclust5	=	pricechangesmmillclust5_lag1	/transformout = (lag 1);
convert	num_pricechangemmillused3p	=	pricechangesmmillused3p_lag1	/transformout = (lag 1);
convert	num_pricechangemmilluseamz	=	pricechangesmmillusedamzn_lag1	/transformout = (lag 1);
convert	num_pricechangeoyamaamzn	=	pricechangesoyamaamzn_lag1	/transformout = (lag 1);
convert	num_pricechangeoyamaclust1	=	pricechangesoyamaclust1_lag1	/transformout = (lag 1);
convert	num_pricechangeoyamaclust2	=	pricechangesoyamaclust2_lag1	/transformout = (lag 1);
convert	num_pricechangeoyamaclust3	=	pricechangesoyamaclust3_lag1	/transformout = (lag 1);
convert	num_pricechangeoyamaclust4	=	pricechangesoyamaclust4_lag1	/transformout = (lag 1);
convert	num_pricechangeoyamaclust5	=	pricechangesoyamaclust5_lag1	/transformout = (lag 1);
convert	num_pricechangeoyamaused3p	=	pricechangesoyamaused3p_lag1	/transformout = (lag 1);
convert	num_pricechangeoyamauseamz	=	pricechangesoyamausedamzn_lag1	/transformout = (lag 1);
convert	num_pricechangepproamzn	=	pricechangespproamzn_lag1	/transformout = (lag 1);
convert	num_pricechangepproclust1	=	pricechangespproclust1_lag1	/transformout = (lag 1);
convert	num_pricechangepproclust2	=	pricechangespproclust2_lag1	/transformout = (lag 1);
convert	num_pricechangepproclust3	=	pricechangespproclust3_lag1	/transformout = (lag 1);
convert	num_pricechangepproclust4	=	pricechangespproclust4_lag1	/transformout = (lag 1);
convert	num_pricechangepproclust5	=	pricechangespproclust5_lag1	/transformout = (lag 1);
convert	num_pricechangepproused3p	=	pricechangespproused3p_lag1	/transformout = (lag 1);
convert	num_pricechangepprouseamz	=	pricechangespprousedamzn_lag1	/transformout = (lag 1);
convert	product_star_EP	=	product_star_EP_lag1	/transformout = (lag 1);
convert	product_star_avbay	=	product_star_avbay_lag1	/transformout = (lag 1);
convert	product_star_breville	=	product_star_brv_lag1	/transformout = (lag 1);
convert	product_star_cosori	=	product_star_cosori_lag1	/transformout = (lag 1);
convert	product_star_crockp	=	product_star_crockp_lag1	/transformout = (lag 1);
convert	product_star_cuisinart	=	product_star_cnart_lag1	/transformout = (lag 1);
convert	product_star_cusimax	=	product_star_cmax_lag1	/transformout = (lag 1);
convert	product_star_elechms	=	product_star_elec_lag1	/transformout = (lag 1);
convert	product_star_gkchf	=	product_star_gkchf_lag1	/transformout = (lag 1);
convert	product_star_hbeach	=	product_star_hbeach_lag1	/transformout = (lag 1);
convert	product_star_mmill	=	product_star_mmill_lag1	/transformout = (lag 1);
convert	product_star_oyama	=	product_star_oyama_lag1	/transformout = (lag 1);
convert	product_star_ppro	=	product_star_ppro_lag1	/transformout = (lag 1);


run;

/*PROC EXPORT */
/*DATA=merge_finaldata2*/
/*DBMS=csv*/
/*OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Home_Kitchen\Cluster Analysis\model_finaldata2_feb2020.csv"*/
/*REPLACE;*/
/*run;*/
PROC EXPORT 
DATA=merge_finaldata2
DBMS=csv
OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Home_Kitchen\Cluster Analysis\model_finaldata2_July2025.csv"
REPLACE;
run;

data inn.model_finaldata2_July2025;
set merge_finaldata2;
run;

PROC CONTENTS DATA = merge_finaldata2;
RUN;

/**Correlation exercise of price changes across sites***/

PROC import 
DATAfile ="D:\Amazon Price Dynamics\New Data\Data Scraped\homedepot_merged\HD_Pricechangesallcateg_4-6-20.csv" 
DBMS=csv
OUT= HD_pricechange_allcateg
REPLACE;
run;

PROC import 
DATAfile ="D:\Amazon Price Dynamics\New Data\Data Scraped\Walmart_merged\walmart_Pricechangesallcateg_4-6-20.csv" 
DBMS=csv
OUT= wm_pricechange_allcateg
REPLACE;
run;

PROC import 
DATAfile ="D:\Amazon Price Dynamics\New Data\Data Scraped\Sears_merged\Sears_Pricechangesallcateg_4-6-20.csv" 
DBMS=csv
OUT= sears_pricechange_allcateg
REPLACE;
run;

data sears_pricechange_allcateg;
set sears_pricechange_allcateg;
rename date = time1;
run;

proc freq data = HD_pricechange_allcateg;
tables time1;
run;

proc freq data = wm_pricechange_allcateg;
tables time1;
run;

proc freq data = sears_pricechange_allcateg;
tables categ;
run;
/*proc print data = HD_pricechange_allcateg (obs=30);*/
/*where categ = 'Deep Fryer';*/
/*run;*/
/**/
/*proc print data = HD (obs=10);*/
/*where categ = 'Deep Fryer';*/
/*run;*/


proc sql;
create table  HD as
select 
time1,
categ,
max(pricechange) as max_pricechange_HD,
mean(pricechange) as mean_pricechange_HD,
min(pricechange) as min_pricechange_HD
from
HD_pricechange_allcateg
/*where time1 >= '14Dec2017'd and time1 <= '06May2018'd */
group by
time1,
categ;
quit;

data HD1;
set HD;
if abs(min_pricechange_HD) > abs(max_pricechange_HD) then maxpricechange_HD = min_pricechange_HD;
else if abs(min_pricechange_HD) < abs(max_pricechange_HD) then maxpricechange_HD = max_pricechange_HD;
else maxpricechange_HD = 0;
run;

proc print data = HD1 (obs=20);
run;

proc sql;
create table WM as
select 
time1,
categ,
max(pricechange) as max_pricechange_wm,
mean(pricechange) as mean_pricechange_wm,
min(pricechange) as min_pricechange_wm
from
wm_pricechange_allcateg
/*where time1 >= '14Dec2017'd and time1 <= '06May2018'd */
group by
time1,
categ;
quit;
data wm1;
set wm;
if abs(min_pricechange_wm) > abs(max_pricechange_wm) then maxpricechange_wm = min_pricechange_wm;
else if abs(min_pricechange_wm) < abs(max_pricechange_wm) then maxpricechange_wm = max_pricechange_wm;
else maxpricechange_wm = 0;
run;
proc freq data = wm1;
tables time1;
run;

proc sql;
create table Sears as
select 
time1,
categ,
max(pricechange) as max_pricechange_sears,
mean(pricechange) as mean_pricechange_sears,
min(pricechange) as min_pricechange_sears
from
sears_pricechange_allcateg
/*where time1 >= '14Dec2017'd and time1 <= '06May2018'd */
group by
time1,
categ;
quit;
data sears1;
set sears;
if abs(min_pricechange_sears) > abs(max_pricechange_sears) then maxpricechange_sears = min_pricechange_sears;
else if abs(min_pricechange_sears) < abs(max_pricechange_sears) then maxpricechange_sears = max_pricechange_sears;
else maxpricechange_sears = 0;
run;
proc freq data = sears1;
tables time1;run;

/*proc print data = sears_pricechange_allcateg ;*/
/*where categ = "Electric Cooker" and time1 = '14Dec2017'd;*/
/*run;*/

data merged_allwebsites (drop= min_pricechange_HD max_pricechange_HD min_pricechange_wm max_pricechange_wm
min_pricechange_sears max_pricechange_sears);
merge HD1 (in=a) WM1 (in=b) Sears1 (in=c);
by time1 categ;
if a and b and c;
run;
/**CREATING PERMANENT DATA SET OF EXTERNAL WEBSITE STACK**/
DATA INN.merged_allwebsites;
SET merged_allwebsites;
RUN;
/*proc print data = merged_allwebsites (obs=20);*/
/*run;*/
/*proc contents data = merged_allwebsites;*/
/*run;*/
/*proc sort data = merged_allwebsites out=merged_allwebsites1;*/
/*by categ;*/
/*run;*/
/*proc corr data = merged_allwebsites1 outp = correlation_output;*/
/*var maxpricechange_HD maxpricechange_WM maxpricechange_sears;*/
/*by categ;*/
/*run;*/
/*proc corr data = merged_allwebsites1 outp = correlation_output;*/
/*var mean_pricechange_HD mean_pricechange_WM mean_pricechange_sears;*/
/*by categ;*/
/*run;*/
/***ONLY FOR ELECTRIC COOKERS*****/
/***choose the average statistics of the external site that is present**/
data externalsite_data ;
set merged_allwebsites;
where categ = 'Electric Cooker';
run;
proc freq data = externalsite_data;
tables time1;
run;
/*data externalsite_data1 (drop=categ);*/
/*set externalsite_data;*/
/*if abs(maxpricechange_HD)= max(abs(maxpricechange_WM),abs(maxpricechange_HD),abs(maxpricechange_sears)) then  maxpricechange_externalsite = maxpricechange_HD;*/
/*else if abs(maxpricechange_WM) = max(abs(maxpricechange_WM),abs(maxpricechange_HD),abs(maxpricechange_sears)) then maxpricechange_externalsite = maxpricechange_WM;*/
/*else if abs(maxpricechange_sears) = max(abs(maxpricechange_WM),abs(maxpricechange_HD),abs(maxpricechange_sears)) then maxpricechange_externalsite = maxpricechange_sears;*/
/*else maxpricechange_externalsite = 0;*/
/*meanpricechange_externalsite = mean(mean_pricechange_HD, mean_pricechange_WM,mean_pricechange_sears);*/
/*run;*/
proc print data = externalsite_data1 (obs=10);
where maxpricechange_externalsite = 0;
run;


/***merging external sites data with the MVRF modeling data prepared***/

/*PROC import */
/*DATAFILE ="C:\Amazon Price Dynamics\New Data\Data Scraped\Home_Kitchen\model_finaldata2_July2025.csv"*/
/*DBMS=csv*/
/*OUT= mvrfmodel_feb2020*/
/*REPLACE;*/
/*run;*/
libname inn 'D:\Amazon Price Dynamics\New Data\Data Scraped\Home_Kitchen\Cluster Analysis';
data merge_finaldata2;
set inn.model_finaldata2_July2025;
/*pricechangescnartusedamzn1_lag1 = input(pricechangescnartusedamzn_lag1,best12.);*/
run;

/*data mvrfdata1  ;*/
/*set mvrfdata;*/
/*run;*/
/*data mvrfdata2;*/
/*set mvrfdata1;*/
/*rename pricechangescnartusedamzn1_lag1 = pricechangescnartusedamzn_lag1;*/
/*if pricechangescnartusedamzn1_lag1 = . then pricechangescnartusedamzn1_lag1 = 0;*/
/*run;*/

proc sort data = merge_finaldata2;
by time1;
run;

/*proc sort data = externalsite_data1 (drop = maxpricechange_HD mean_pricechange_HD */
/*maxpricechange_WM mean_pricechange_WM */
/*maxpricechange_sears mean_pricechange_sears)*/
/*out=externalsite_data2;*/
/*by time1;*/
/*run;*/

proc sort data = externalsite_data (drop = categ) out = externalsite_data2;
by time1;
run;
proc contents data = externalsite_data2;
run;
/**/
/*data mvrfdata_withexternalsite;*/
/*merge mvrf_model (in=a) externalsite_data2 (in=b);*/
/*by time1;*/
/*if a;*/
/*if a and not b then do;*/
/*externalsite_ind = 0;*/
/*maxpricechange_extsite_lag1 = 0;*/
/*meanpricechange_extsite_lag1 = 0;*/
/*end;*/
/*else externalsite_ind = 1;*/
/*run;*/

data mvrfdata_withexternalsite;
merge merge_finaldata2 (in=a) externalsite_data2 (in=b);
by time1;
if a;

if a and not b then do;
ext_site = 0;
maxpricechange_HD = 0;   
maxpricechange_sears = 0;     
maxpricechange_wm = 0;     
mean_pricechange_HD = 0;     
mean_pricechange_sears = 0;     
mean_pricechange_wm = 0;
end;
else ext_site =1;
run;
proc freq data = mvrfdata_withexternalsite;
tables ext_site;
run;
PROC PRINT DATA = mvrfdata_withexternalsite (OBS=200);
VAR priceclust3_CrockP priceclust4_Breville priceclust5_CrockP;
RUN;
/*proc print data = mvrfdata_withexternalsite (obs=10);*/
/*where maxpricechange_externalsite = 0;*/
/*run;*/
/*proc contents data = mvrfdata_withexternalsite;*/
/*run;*/
/**old data with aggregated external site****/
/*PROC EXPORT */
/*DATA=mvrfdata_withexternalsite*/
/*DBMS=csv*/
/*OUTFILE= "C:\Users\F00456N\Documents\Amazon Price Dynamics\New Data\Data Scraped\Home_Kitchen\Cluster Analysis\mvrfdata_withexternalsite_feb2020.csv"*/
/*REPLACE;*/
/*run;*/


/****NEW VARIABLES TO BE ADDED AS FIXED EFFECTS POST JM REVIEW***/
/**/
/*PROC import */
/*DATAFILE ="D:\Amazon Price Dynamics\New Data\Data Scraped\Home_Kitchen\Cluster Analysis\mvrfdata_withexternalsite_feb2020.csv"*/
/*DBMS=csv*/
/*OUT= mvrfdata_withexternalsite*/
/*REPLACE;*/
/*run;*/
/*proc contents data = mvrfdata_withexternalsite;*/
/*run;*/


/***DAY OF THE WEEK IDENTIFICATION**/

data mvrfdata_withexternalsite1;
set mvrfdata_withexternalsite;
day_week = weekday(time1);
if day_week = 1 then sunday = 1; else sunday = 0;
if day_week = 2 then monday = 1; else monday = 0;
if day_week = 3 then tuesday = 1; else tuesday = 0;
if day_week = 4 then wednesday = 1; else wednesday = 0;
if day_week = 5 then thursday = 1; else thursday = 0;
if day_week = 6 then friday = 1; else friday = 0;
if day_week = 7 then saturday = 1; else saturday = 0;
if day_week in (1,7) then weekend = 1; else weekend = 0;
run;

proc print data = mvrfdata_withexternalsite1 (obs=10);
var time1 weekend;
run;
/***lagging external site price changes****/ 
data mvrfdata_withexternalsite2;
set mvrfdata_withexternalsite1;
maxpricechange_HDlag1 = lag(maxpricechange_HD);   
maxpricechange_searslag1 = lag(maxpricechange_sears);   
maxpricechange_wmlag1 = lag(maxpricechange_wm);
maxpricechange_HDlag2 = lag2(maxpricechange_HD);   
maxpricechange_searslag2 = lag2(maxpricechange_sears);   
maxpricechange_wmlag2 = lag2(maxpricechange_wm);
maxpricechange_HDlag3 = lag3(maxpricechange_HD);   
maxpricechange_searslag3 = lag3(maxpricechange_sears);   
maxpricechange_wmlag3 = lag3(maxpricechange_wm);
run;


/**SALES RANK CATEGORIZATION: RELATIVE RANKING OF BRANDS IN THE LAST PERIOD***/

data mvrfdata_withexternalsite3;
set mvrfdata_withexternalsite2;
top_salesrank = min(ifn(mean_salesrank_categ_IP_lag1=0,.,mean_salesrank_categ_IP_lag1),
                    ifn(min_salesrank_categ_IP_lag1=0,.,min_salesrank_categ_IP_lag1),
                    ifn(max_salesrank_categ_IP_lag1=0,.,max_salesrank_categ_IP_lag1), 
                    ifn(salesrank_cat_EP_lag1=0,.,salesrank_cat_EP_lag1),
/*                    ifn(salesrank_cat_avbay_lag1=0,.,salesrank_cat_avbay_lag1), */
                    ifn(salesrank_cat_brv_lag1=0,.,salesrank_cat_brv_lag1),
/*                    ifn(salesrank_cat_cmax_lag1=0,.,salesrank_cat_cmax_lag1),*/
/*                    ifn(salesrank_cat_cnart_lag1=0,.,salesrank_cat_cnart_lag1),*/
                    ifn(salesrank_cat_cosori_lag1=0,.,salesrank_cat_cosori_lag1),
                    ifn(salesrank_cat_crockp_lag1=0,.,salesrank_cat_crockp_lag1))
/*                    ifn(salesrank_cat_elec_lag1=0,.,salesrank_cat_elec_lag1),*/
/*                    ifn(salesrank_cat_gkchf_lag1=0,.,salesrank_cat_gkchf_lag1),*/
/*                    ifn(salesrank_cat_hbeach_lag1=0,.,salesrank_cat_hbeach_lag1),*/
/*                    ifn(salesrank_cat_mmill_lag1=0,.,salesrank_cat_mmill_lag1), */
/*                    ifn(salesrank_cat_oyama_lag1=0,.,salesrank_cat_oyama_lag1),*/
/*                    ifn(salesrank_cat_ppro_lag1=0,.,salesrank_cat_ppro_lag1)*/
;

bottom_salesrank = max(ifn(mean_salesrank_categ_IP_lag1=0,.,mean_salesrank_categ_IP_lag1),
                    ifn(min_salesrank_categ_IP_lag1=0,.,min_salesrank_categ_IP_lag1),
                    ifn(max_salesrank_categ_IP_lag1=0,.,max_salesrank_categ_IP_lag1), 
                    ifn(salesrank_cat_EP_lag1=0,.,salesrank_cat_EP_lag1),
/*                    ifn(salesrank_cat_avbay_lag1=0,.,salesrank_cat_avbay_lag1), */
                    ifn(salesrank_cat_brv_lag1=0,.,salesrank_cat_brv_lag1),
/*                    ifn(salesrank_cat_cmax_lag1=0,.,salesrank_cat_cmax_lag1),*/
/*                    ifn(salesrank_cat_cnart_lag1=0,.,salesrank_cat_cnart_lag1),*/
                    ifn(salesrank_cat_cosori_lag1=0,.,salesrank_cat_cosori_lag1),
                    ifn(salesrank_cat_crockp_lag1=0,.,salesrank_cat_crockp_lag1))
/*                    ifn(salesrank_cat_elec_lag1=0,.,salesrank_cat_elec_lag1),*/
/*                    ifn(salesrank_cat_gkchf_lag1=0,.,salesrank_cat_gkchf_lag1),*/
/*                    ifn(salesrank_cat_hbeach_lag1=0,.,salesrank_cat_hbeach_lag1),*/
/*                    ifn(salesrank_cat_mmill_lag1=0,.,salesrank_cat_mmill_lag1), */
/*                    ifn(salesrank_cat_oyama_lag1=0,.,salesrank_cat_oyama_lag1),*/
/*                    ifn(salesrank_cat_ppro_lag1=0,.,salesrank_cat_ppro_lag1))*/
;

run;
proc print data = mvrfdata_withexternalsite3 (obs=10);
var bottom_salesrank;
run;


data mvrfdata_withexternalsite4;
set mvrfdata_withexternalsite3;
if top_salesrank ne . and top_salesrank ne bottom_salesrank then do;
if min_salesrank_categ_IP_lag1 = top_salesrank then ip_topbrandlag1 = 1; else ip_topbrandlag1 = 0; 
if salesrank_cat_EP_lag1 = top_salesrank then ep_topbrandlag1 = 1; else ep_topbrandlag1 = 0; 
if salesrank_cat_brv_lag1 = top_salesrank then brv_topbrandlag1 = 1; else brv_topbrandlag1 = 0; 
if salesrank_cat_cosori_lag1 = top_salesrank then csri_topbrandlag1 = 1; else csri_topbrandlag1 = 0; 
if salesrank_cat_crockp_lag1 = top_salesrank then crockp_topbrandlag1 = 1; else crockp_topbrandlag1 = 0;
end;

if bottom_salesrank ne . and top_salesrank ne bottom_salesrank then do;
if min_salesrank_categ_IP_lag1 = bottom_salesrank then ip_bottombrandlag1 = 1; else ip_bottombrandlag1 = 0; 
if salesrank_cat_EP_lag1 = bottom_salesrank then ep_bottombrandlag1 = 1; else ep_bottombrandlag1 = 0; 
if salesrank_cat_brv_lag1 = bottom_salesrank then brv_bottombrandlag1 = 1; else brv_bottombrandlag1 = 0; 
if salesrank_cat_cosori_lag1 = bottom_salesrank then csri_bottombrandlag1 = 1; else csri_bottombrandlag1 = 0; 
if salesrank_cat_crockp_lag1 = bottom_salesrank then crockp_bottombrandlag1 = 1; else crockp_bottombrandlag1 = 0;
end;

else do;
ip_topbrandlag1 = 0; 
ep_topbrandlag1 = 0; 
brv_topbrandlag1 = 0; 
csri_topbrandlag1 = 0; 
crockp_topbrandlag1 = 0;
ip_bottombrandlag1 = 0; 
ep_bottombrandlag1 = 0; 
brv_bottombrandlag1 = 0; 
csri_bottombrandlag1 = 0; 
crockp_bottombrandlag1 = 0;
end;

run;

proc print data = mvrfdata_withexternalsite4 (obs=10);
var 
ip_topbrandlag1 
ep_topbrandlag1 
brv_topbrandlag1
csri_topbrandlag1
crockp_topbrandlag1 
ip_bottombrandlag1 
ep_bottombrandlag1 
brv_bottombrandlag1
csri_bottombrandlag1 
crockp_bottombrandlag1;
run;
proc contents data = mvrfdata_withexternalsite3;
run;

/**price changes made in breville**/
/*data abs_price_changes;*/
/*set mvrfdata_withexternalsite4;*/
/*abs_amzn_breville_lag1 = abs(Y_amzn_breville_lag1);*/
/*abs_clust4_breville_lag1 = abs(Y_clust4_breville_lag1);*/
/*abs_clust1_breville_lag1 = abs(Y_clust1_breville_lag1);*/
/*abs_amzn_cosori_lag1 = abs(Y_amzn_cosori_lag1);*/
/*abs_amzn_ep_lag1 = abs(Y_amzn_ep_lag1);*/
/*abs_clust2_ep_lag1 = abs(Y_clust2_ep_lag1);*/
/*abs_amzn_ip_lag1 = abs(Y_amzn_ip_lag1);*/
/*abs_clust3_crockp_lag1 = abs(Y_clust3_crockp_lag1);*/
/*abs_clust5_crockp_lag1 = abs(Y_clust5_crockp_lag1);*/
/*run;*/
proc contents data = mvrfdata_withexternalsite4;
run;


/***creating main effect of price change**/
data final_gam_modeldata;
set mvrfdata_withexternalsite4;

/*price increase/decrease of 5% and -5%, with direction**/

/*breville*/
if Y_amzn_breville_lag1 >= 0.05 and  Y_amzn_breville_lag1 < 0.1 then incr5per_amzn_brev = 1; else incr5per_amzn_brev = 0;
if Y_clust1_breville_lag1 >= 0.05 and Y_clust1_breville_lag1 < 0.1 then incr5per_clust1_brev = 1; else incr5per_clust1_brev = 0;
if Y_clust4_breville_lag1 >= 0.05 and Y_clust4_breville_lag1 < 0.1 then incr5per_clust4_brev = 1; else incr5per_clust4_brev = 0;

if Y_amzn_breville_lag1 <= -0.05 and Y_amzn_breville_lag1 > -0.1 then decr5per_amzn_brev = 1; else decr5per_amzn_brev = 0;
if Y_clust1_breville_lag1 <= -0.05 and Y_clust1_breville_lag1 > -0.1 then decr5per_clust1_brev = 1; else decr5per_clust1_brev = 0;
if Y_clust4_breville_lag1 <= -0.05 and Y_clust4_breville_lag1 > -0.1 then decr5per_clust4_brev = 1; else decr5per_clust4_brev = 0;

/*cosori*/
if Y_amzn_cosori_lag1 >= 0.05 and Y_amzn_cosori_lag1 < 0.1 then incr5per_amzn_csri = 1; else incr5per_amzn_csri = 0;
if Y_amzn_cosori_lag1 <= -0.05 and Y_amzn_cosori_lag1 > -0.1 then decr5per_amzn_csri = 1; else decr5per_amzn_csri = 0;

/*elite platinum*/
if Y_amzn_ep_lag1 >= 0.05 and Y_amzn_ep_lag1 < 0.1 then incr5per_amzn_ep = 1; else incr5per_amzn_ep = 0;
if Y_clust2_ep_lag1 >= 0.05 and Y_clust2_ep_lag1 < 0.1 then incr5per_3p_ep = 1; else incr5per_3p_ep = 0;

if Y_amzn_ep_lag1 <= -0.05 and Y_amzn_ep_lag1 > -0.1 then decr5per_amzn_ep = 1; else decr5per_amzn_ep = 0;
if Y_clust2_ep_lag1 <= -0.05 and Y_clust2_ep_lag1 > -0.1 then decr5per_3p_ep = 1; else decr5per_3p_ep = 0;

/*instant pot*/
if Y_amzn_ip_lag1 >= 0.05 and Y_amzn_ip_lag1 < 0.1 then incr5per_amzn_ip = 1; else incr5per_amzn_ip = 0;
if Y_amzn_ip_lag1 <= -0.05 and Y_amzn_ip_lag1 > -0.1 then decr5per_amzn_ip = 1; else decr5per_amzn_ip = 0;

/*crock pot*/

if Y_amzn_crockp_lag1 >= 0.05 and Y_amzn_crockp_lag1 < 0.1 then incr5per_amzn_crockp = 1; else incr5per_amzn_crockp = 0;
if Y_clust3_crockp_lag1 >= 0.05 and Y_clust3_crockp_lag1 < 0.1 then incr5per_clust3_crockp = 1; else incr5per_clust3_crockp = 0;
if Y_clust5_crockp_lag1 >= 0.05 and Y_clust5_crockp_lag1 < 0.1 then incr5per_clust5_crockp = 1; else incr5per_clust5_crockp = 0;

if Y_amzn_crockp_lag1 <= -0.05 and Y_amzn_crockp_lag1 > -0.1 then decr5per_amzn_crockp = 1; else decr5per_amzn_crockp = 0;
if Y_clust3_crockp_lag1 <= -0.05 and Y_clust3_crockp_lag1 > -0.1 then decr5per_clust3_crockp = 1; else decr5per_clust3_crockp = 0;
if Y_clust5_crockp_lag1 <= -0.05 and Y_clust5_crockp_lag1 > -0.1 then decr5per_clust5_crockp = 1; else decr5per_clust5_crockp = 0;

/*price increase/decrease of 10% and -10%, with direction**/

/*breville*/
if Y_amzn_breville_lag1 >= 0.1 and Y_amzn_breville_lag1 < 0.2 then incr10per_amzn_brev = 1; else incr10per_amzn_brev = 0;
if Y_clust1_breville_lag1 >= 0.1 and Y_clust1_breville_lag1 < 0.2 then incr10per_clust1_brev = 1; else incr10per_clust1_brev = 0;
if Y_clust4_breville_lag1 >= 0.1 and Y_clust4_breville_lag1 < 0.2 then incr10per_clust4_brev = 1; else incr10per_clust4_brev = 0;


if Y_amzn_breville_lag1 <= -0.1 and Y_amzn_breville_lag1 > -0.2 then decr10per_amzn_brev = 1; else decr10per_amzn_brev = 0;
if Y_clust1_breville_lag1 <= -0.1 and Y_clust1_breville_lag1 > -0.2 then decr10per_clust1_brev = 1; else decr10per_clust1_brev = 0;
if Y_clust4_breville_lag1 <= -0.1 and Y_clust4_breville_lag1 > -0.2 then decr10per_clust4_brev = 1; else decr10per_clust4_brev = 0;

/*cosori*/
if Y_amzn_cosori_lag1 >= 0.1 and Y_amzn_cosori_lag1 < 0.2 then incr10per_amzn_csri = 1; else incr10per_amzn_csri = 0;
if Y_amzn_cosori_lag1 <= -0.1 and Y_amzn_cosori_lag1 > -0.2 then decr10per_amzn_csri = 1; else decr10per_amzn_csri = 0;

/*elite platinum*/
if Y_amzn_ep_lag1 >= 0.1 and Y_amzn_ep_lag1 < 0.2 then incr10per_amzn_ep = 1; else incr10per_amzn_ep = 0;
if Y_clust2_ep_lag1 >= 0.1 and Y_clust2_ep_lag1 < 0.2 then incr10per_3p_ep = 1; else incr10per_3p_ep = 0;

if Y_amzn_ep_lag1 <= -0.1 and Y_amzn_ep_lag1 > -0.2 then decr10per_amzn_ep = 1; else decr10per_amzn_ep = 0;
if Y_clust2_ep_lag1 <= -0.1 and Y_clust2_ep_lag1 > -0.2 then decr10per_3p_ep = 1; else decr10per_3p_ep = 0;

/*instant pot*/
if Y_amzn_ip_lag1 >= 0.1 and Y_amzn_ip_lag1 < 0.2 then incr10per_amzn_ip = 1; else incr10per_amzn_ip = 0;
if Y_amzn_ip_lag1 <= -0.1 and Y_amzn_ip_lag1 > -0.2 then decr10per_amzn_ip = 1; else decr10per_amzn_ip = 0;

/*crock pot*/
if Y_amzn_crockp_lag1 >= 0.1 and Y_amzn_crockp_lag1 < 0.2 then incr10per_amzn_crockp = 1; else incr10per_amzn_crockp = 0;
if Y_clust3_crockp_lag1 >= 0.1 and Y_clust3_crockp_lag1 < 0.2 then incr10per_clust3_crockp = 1; else incr10per_clust3_crockp = 0;
if Y_clust5_crockp_lag1 >= 0.1 and Y_clust5_crockp_lag1 < 0.2 then incr10per_clust5_crockp = 1; else incr10per_clust5_crockp = 0;

if Y_amzn_crockp_lag1 <= -0.1 and Y_amzn_crockp_lag1 > -0.2 then decr10per_amzn_crockp = 1; else decr10per_amzn_crockp = 0;
if Y_clust3_crockp_lag1 <= -0.1 and Y_clust3_crockp_lag1 > -0.2 then decr10per_clust3_crockp = 1; else decr10per_clust3_crockp = 0;
if Y_clust5_crockp_lag1 <= -0.1 and Y_clust5_crockp_lag1 > -0.2 then decr10per_clust5_crockp = 1; else decr10per_clust5_crockp = 0;

/*price increase/decrease of 20% and -20%, with direction**/

/*breville*/
if Y_amzn_breville_lag1 >= 0.2 then incr20per_amzn_brev = 1; else incr20per_amzn_brev = 0;
if Y_clust1_breville_lag1 >= 0.2 then incr20per_clust1_brev = 1; else incr20per_clust1_brev = 0;
if Y_clust4_breville_lag1 >= 0.2 then incr20per_clust4_brev = 1; else incr20per_clust4_brev = 0;

if Y_amzn_breville_lag1 <= -0.2 then decr20per_amzn_brev = 1; else decr20per_amzn_brev = 0;
if Y_clust1_breville_lag1 <= -0.2 then decr20per_clust1_brev = 1; else decr20per_clust1_brev = 0;
if Y_clust4_breville_lag1 <= -0.2 then decr20per_clust4_brev = 1; else decr20per_clust4_brev = 0;

/*cosori*/
if Y_amzn_cosori_lag1 >= 0.2 then incr20per_amzn_csri = 1; else incr20per_amzn_csri = 0;
if Y_amzn_cosori_lag1 <= -0.2 then decr20per_amzn_csri = 1; else decr20per_amzn_csri = 0;

/*elite platinum*/
if Y_amzn_ep_lag1 >= 0.2 then incr20per_amzn_ep = 1; else incr20per_amzn_ep = 0;
if Y_clust2_ep_lag1 >= 0.2 then incr20per_3p_ep = 1; else incr20per_3p_ep = 0;

if Y_amzn_ep_lag1 <= -0.2 then decr20per_amzn_ep = 1; else decr20per_amzn_ep = 0;
if Y_clust2_ep_lag1 <= -0.2 then decr20per_3p_ep = 1; else decr20per_3p_ep = 0;

/*instant pot*/
if Y_amzn_ip_lag1 >= 0.2 then incr20per_amzn_ip = 1; else incr20per_amzn_ip = 0;
if Y_amzn_ip_lag1 <= -0.2 then decr20per_amzn_ip = 1; else decr20per_amzn_ip = 0;

/*crock pot*/
if Y_amzn_crockp_lag1 >= 0.2 then incr20per_amzn_crockp = 1; else incr20per_amzn_crockp = 0;
if Y_clust3_crockp_lag1 >= 0.2 then incr20per_clust3_crockp = 1; else incr20per_clust3_crockp = 0;
if Y_clust5_crockp_lag1 >= 0.2 then incr20per_clust5_crockp = 1; else incr20per_clust5_crockp = 0;

if Y_amzn_crockp_lag1 <= -0.2 then decr20per_amzn_crockp = 1; else decr20per_amzn_crockp = 0;
if Y_clust3_crockp_lag1 <= -0.2 then decr20per_clust3_crockp = 1; else decr20per_clust3_crockp = 0;
if Y_clust5_crockp_lag1 <= -0.2 then decr20per_clust5_crockp = 1; else decr20per_clust5_crockp = 0;

/**need to incorporate top and bottom brands as interaction effects in the GAM*/
run;

proc univariate data = final_gam_modeldata ;
var Y_amzn_crockp_lag1;
run;
/**/
/*proc sql;*/
/*create table summ_num_changes as*/
/*select*/
/*sum(case when Y_amzn_breville_lag1 ne 0 then 1 else 0 end) as num_amzn_brevillechanges,*/
/*sum(case when Y_clust4_breville_lag1 ne 0 then 1 else 0 end) as num_3p_brevillechanges,*/
/*sum(case when Y_amzn_cosori_lag1 ne 0 then 1 else 0 end) as num_amzn_csrichanges,*/
/*sum(case when Y_amzn_ep_lag1 ne 0 then 1 else 0 end) as num_amzn_epchanges,*/
/*sum(case when Y_clust2_ep_lag1 ne 0 then 1 else 0 end) as num_3p_epchanges,*/
/*sum(case when Y_amzn_ip_lag1 ne 0 then 1 else 0 end) as num_amzn_ipchanges,*/
/*sum(case when Y_clust3_crockp_lag1 ne 0 then 1 else 0 end) as num_3p_crockpchanges,*/
/*sum(case when Y_clust5_crockp_lag1 ne 0 then 1 else 0 end) as num_3p2_crockpchanges,*/
/*sum(case when incr20per_amzn_brev ne 0 then 1 else 0 end) as num_incr20peramzn_brevchanges,*/
/*sum(case when decr20per_amzn_brev ne 0 then 1 else 0 end) as num_decr20peramzn_brevchanges,*/
/*sum(case when incr10per_amzn_brev ne 0 then 1 else 0 end) as num_incr10peramzn_brevchanges,*/
/*sum(case when decr10per_amzn_brev ne 0 then 1 else 0 end) as num_decr10peramzn_brevchanges,*/
/*sum(case when incr5per_amzn_brev ne 0 then 1 else 0 end) as num_incr5peramzn_brevchanges,*/
/*sum(case when decr5per_amzn_brev ne 0 then 1 else 0 end) as num_decr5peramzn_brevchanges,*/
/*sum(case when incr20per_3p_brev ne 0 then 1 else 0 end) as num_incr20per3p_brevchanges,*/
/*sum(case when decr20per_3p_brev ne 0 then 1 else 0 end) as num_decr20per3p_brevchanges,*/
/*sum(case when incr10per_3p_brev ne 0 then 1 else 0 end) as num_incr10per3p_brevchanges,*/
/*sum(case when decr10per_3p_brev ne 0 then 1 else 0 end) as num_decr10per3p_brevchanges,*/
/*sum(case when incr5per_3p_brev ne 0 then 1 else 0 end) as num_incr5per3p_brevchanges,*/
/*sum(case when decr5per_3p_brev ne 0 then 1 else 0 end) as num_decr5per3p_brevchanges,*/
/*sum(case when incr20per_amzn_csri ne 0 then 1 else 0 end) as num_incr20peramzn_csrichanges,*/
/*sum(case when decr20per_amzn_csri ne 0 then 1 else 0 end) as num_decr20peramzn_csrichanges,*/
/*sum(case when incr10per_amzn_csri ne 0 then 1 else 0 end) as num_incr10peramzn_csrichanges,*/
/*sum(case when decr10per_amzn_csri ne 0 then 1 else 0 end) as num_decr10peramzn_csrichanges,*/
/*sum(case when incr5per_amzn_csri ne 0 then 1 else 0 end) as num_incr5peramzn_csrichanges,*/
/*sum(case when decr5per_amzn_csri ne 0 then 1 else 0 end) as num_decr5peramzn_csrichanges,*/
/*sum(case when incr20per_amzn_ep ne 0 then 1 else 0 end) as num_incr20peramzn_epchanges,*/
/*sum(case when decr20per_amzn_ep ne 0 then 1 else 0 end) as num_decr20peramzn_epchanges,*/
/*sum(case when incr10per_amzn_ep ne 0 then 1 else 0 end) as num_incr10peramzn_epchanges,*/
/*sum(case when decr10per_amzn_ep ne 0 then 1 else 0 end) as num_decr10peramzn_epchanges,*/
/*sum(case when incr5per_amzn_ep ne 0 then 1 else 0 end) as num_incr5peramzn_epchanges,*/
/*sum(case when decr5per_amzn_ep ne 0 then 1 else 0 end) as num_decr5peramzn_epchanges,*/
/*sum(case when incr20per_3p_ep ne 0 then 1 else 0 end) as num_incr20per3p_epchanges,*/
/*sum(case when decr20per_3p_ep ne 0 then 1 else 0 end) as num_decr20per3p_epchanges,*/
/*sum(case when incr10per_3p_ep ne 0 then 1 else 0 end) as num_incr10per3p_epchanges,*/
/*sum(case when decr10per_3p_ep ne 0 then 1 else 0 end) as num_decr10per3p_epchanges,*/
/*sum(case when incr5per_3p_ep ne 0 then 1 else 0 end) as num_incr5per3p_epchanges,*/
/*sum(case when decr5per_3p_ep ne 0 then 1 else 0 end) as num_decr5per3p_epchanges,*/
/*sum(case when incr20per_amzn_ip ne 0 then 1 else 0 end) as num_incr20peramzn_ipchanges,*/
/*sum(case when decr20per_amzn_ip ne 0 then 1 else 0 end) as num_decr20peramzn_ipchanges,*/
/*sum(case when incr10per_amzn_ip ne 0 then 1 else 0 end) as num_incr10peramzn_ipchanges,*/
/*sum(case when decr10per_amzn_ip ne 0 then 1 else 0 end) as num_decr10peramzn_ipchanges,*/
/*sum(case when incr5per_amzn_ip ne 0 then 1 else 0 end) as num_incr5peramzn_ipchanges,*/
/*sum(case when decr5per_amzn_ip ne 0 then 1 else 0 end) as num_decr5peramzn_ipchanges,*/
/*sum(case when incr20per_3p_crockp ne 0 then 1 else 0 end) as num_incr20per3p_crockpchanges,*/
/*sum(case when decr20per_3p_crockp ne 0 then 1 else 0 end) as num_decr20per3p_crockpchanges,*/
/*sum(case when incr10per_3p_crockp ne 0 then 1 else 0 end) as num_incr10per3p_crockpchanges,*/
/*sum(case when decr10per_3p_crockp ne 0 then 1 else 0 end) as num_decr10per3p_crockpchanges,*/
/*sum(case when incr5per_3p_crockp ne 0 then 1 else 0 end) as num_incr5per3p_crockpchanges,*/
/*sum(case when decr5per_3p_crockp ne 0 then 1 else 0 end) as num_decr5per3p_crockpchanges*/
/*from */
/*final_gam_modeldata;*/
/*quit;*/
proc print data = summ_num_changes;
run;

proc freq data = final_gam_modeldata;
tables
/*abs10per_amzn_brev*/
/*abs10per_3p_brev*/
/*abs10per_amzn_csri*/
/*abs10per_amzn_ep*/
/*abs10per_3p_ep*/
/*abs10per_amzn_ip*/
/*abs10per_3p_crockp;*/
/*incr5per_amzn_brev*/
/*incr5per_3p_brev*/
/*decr5per_amzn_brev*/
/*decr5per_3p_brev*/
/*incr5per_amzn_csri*/
/*decr5per_amzn_csri*/
/*incr5per_amzn_ep*/
/*incr5per_3p_ep*/
/*decr5per_amzn_ep*/
/*decr5per_3p_ep*/
/*incr5per_amzn_ip*/
/*decr5per_amzn_ip*/
/*incr5per_3p_crockp*/
/*decr5per_3p_crockp;*/

incr20per_amzn_brev
incr20per_3p_brev
decr20per_amzn_brev
decr20per_3p_brev
incr20per_amzn_csri
decr20per_amzn_csri
incr20per_amzn_ep
incr20per_3p_ep
decr20per_amzn_ep
decr20per_3p_ep
incr20per_amzn_ip
decr20per_amzn_ip
incr20per_3p_crockp
decr20per_3p_crockp;
run;

/*PROC EXPORT */
/*DATA=final_gam_modeldata*/
/*DBMS=csv*/
/*OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Home_Kitchen\Cluster Analysis\final_gam_modeldata.csv"*/
/*REPLACE;*/
/*run;*/

/*PROC EXPORT */
/*DATA=final_gam_modeldata*/
/*DBMS=csv*/
/*OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Home_Kitchen\Cluster Analysis\gam_modeldata_3_14_2025_JMRnd4.csv"*/
/*REPLACE;*/
/*run;*/

PROC EXPORT 
DATA=final_gam_modeldata
DBMS=csv
OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Home_Kitchen\Cluster Analysis\gam_modeldata_July_2025_JMRnd4.csv"
REPLACE;
run;

proc contents data = final_gam_modeldata;
run;
data inn.final_gam_modeldata;
set final_gam_modeldata;
run;


/*proc print data = final_gam_modeldata (obs=200);*/
/*var priceAmzn_Breville*/
/*priceAmzn_Cosori*/
/*priceAmzn_EP*/
/*priceAmzn_IP*/
/*priceclust1_Breville*/
/*priceclust2_EP*/
/*priceclust3_CrockP*/
/*priceclust4_Breville*/
/*priceclust5_CrockP*/
/*pricediff_lowst_Amzn_Breville*/
/*pricediff_lowst_Amzn_Cosori*/
/*pricediff_lowst_Amzn_EP*/
/*pricediff_lowst_Amzn_IP*/
/*pricediff_lowst_clust1_Breville*/
/*pricediff_lowst_clust2_EP*/
/*pricediff_lowst_clust3_CrockP*/
/*pricediff_lowst_clust4_Breville*/
/*pricediff_lowst_clust5_CrockP*/
/*priceclust4_Breville priceclust3_crockp*/
/*nonbbox3pprice_brev_lag;*/
/*run;*/
